How to make a stored procedure loop

[vamoose]

Can someone please tell or show me the basic principle on how to make a MSSQL 2000 stored procedure do a loop. Something like in the VBA example:

For Tester = 1 to 30
If something then something
Next Tester

Thanks for your assistance

 

[ca8msm]

You could use a while loop.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

HOwever, if at all possible it is best to avoid doing loops becasue loops are performance hogs. What are you planning on doing in the loop, it may be possible to do it in a set-based fashion instead.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

Here the example:

DECLARE @Tester int
SET @Tester = 1
WHILE @Tester < 31
      BEGIN
        IF something
           BEGIN
           END
        ELSE
           BEGIN
           END

        SET @Tester = @Tester + 1
      END

but I am with SQL Sister here. Try to avoid loops if you could do what you want with set based commands.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP