How can i run a Stored proc in a loop

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 2008 Forums

Transact-SQL (2008)

How can i run a Stored proc in a loop

Author

Topic

Pilot_Riaz
Starting Member

16 Posts

Posted - 2011-02-23 : 11:10:38

Hi,

There is stored proc UpdateTerminalEnabledState which accepts 2 params, ArbiterTermID and EnabledState
If i run this procedure passing in a ArbiterTermID (100) and EnableState (0)
This will add an entry to a table with the values 100, 0

I then need to run the procedure again with params ArbiterTermID (100) and EnableState (1)
I then need to run procedure again with params 100, 0

Can i execute this procedure in a loop to run say 500 times.
The stored proc will then populate the table with the values
100, 0
100, 1
100, 0
100, 1
etc

e.g. exec UpdateTerminalEnabledState 100 0
exec UpdateTerminalEnabledState 100 1
exec UpdateTerminalEnabledState 100 0
exec UpdateTerminalEnabledState 100 1

How can i run the stored proc in a loop please so i don't have to write it 500 times to run 500 times?

ALTER PROCEDURE [CommandUnit].[UpdateTerminalEnabledState]
@ArbiterTerminalID int,
@EnabledState tinyint

Riaz

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-02-23 : 11:52:42

http://msdn.microsoft.com/en-us/library/ms178642.aspx

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-23 : 12:36:16

If that's all the stored procedure does, there are far better and faster ways to populate that pattern of data in the table.

Lewie
Starting Member

42 Posts

Posted - 2011-02-24 : 09:46:11

declare @Script varchar(max),
@counter int

set @counter = 500

while @counter > 0
BEGIN
select @Script = ISNULL(@Script,'') + 'exec UpdateTerminalEnabledState 100,1;exec UpdateTerminalEnabledState 100,0;',
@counter = @counter - 1
end

exec (@Script)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-24 : 11:09:33

As told by Robvolk, this can be done easily

insert into table(col1,col2)
select 100,0 from master..spt_values where type='p' and number between 1 and 500
union all
select 100,1 from master..spt_values where type='p' and number between 1 and 500

You can use tally number table if you have it.
Also what is the need of duplicating the data in the table?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-24 : 11:15:06

or simply

insert into table(col1,col2)
select 100,number%2 from master..spt_values where type='p' and number between 1 and 1000

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources