How to use the Evaluate Statement in a Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to use the Evaluate Statement in a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-12 : 09:00:34

Wayne writes "I need to use the Eval() statement in a stroed proceure so that i can loop through variable names.
This can be explained by the example below

I have to Call this SP 29 times instead of Using a while loop and inclementing @counter every time.
These values are declered above

exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots1, @TimeSlotc1, 1
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots2, @TimeSlotc2,2
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots3, @TimeSlotc3,3
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots4, @TimeSlotc4,4
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots5, @TimeSlotc5,5
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots6, @TimeSlotc6,6
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots7, @TimeSlotc7,7
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots8, @TimeSlotc8,8
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots9, @TimeSlotc9,9
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots10, @TimeSlotc10,10
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots11, @TimeSlotc11,11
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots12, @TimeSlotc12,12
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots13, @TimeSlotc13,13
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots14, @TimeSlotc14,14
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots15, @TimeSlotc15,15
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots16, @TimeSlotc16,16
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots17, @TimeSlotc17,17
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots18, @TimeSlotc18,18
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots19, @TimeSlotc19,19
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots20, @TimeSlotc20,20
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots21, @TimeSlotc21,21
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots22, @TimeSlotc22,22
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots23, @TimeSlotc23,23
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots24, @TimeSlotc24,24
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots25, @TimeSlotc25,25
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots26, @TimeSlotc26,26
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots27, @TimeSlotc27,27
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots28, @TimeSlotc28,28
exec UpdateIdealResourceForEachTimeSlot @OU_ID, @OU_TYP, @DayOfWeek, @TimeSlots29, @TimeSlotc29,29
GO

How cani use the @counter value to eval() theses 3 values @TimeSlots28, @TimeSlotc28,28"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-12 : 09:32:00

How about passing 29 values in a comma-separated string (CSV), then parsing them out? We have a number of articles on CSV parsing:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

This might help too:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538

You could pass all of the values in one string, parse them out into individual pieces, and process them all at once. If your "UpdateIdealResourceForEachTimeSlot" stored procedure only handles one value at a time, you can post your code, and we can try to rewrite it so that it can process an entire list (of any length) in one shot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources