Saturday, 3 October 2009

TSQL : While Loop (loop without cursor)

Really simple this, but i keep using so I thought I'd post.
How to loop in TSQL (without a CURSOR)
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @MaxCount INT
DECLARE @Stepsize INT

SELECT @Counter = 0
SELECT @MaxCount = 40000
SELECT @Stepsize = 1000

WHILE @Counter <= @MaxCount  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
 -- do useful stuff here
 SELECT @Counter = @Counter + @Stepsize 
END 
and counting down....
DECLARE @Counter INT
DECLARE @CounterText VARCHAR(30)
DECLARE @MinCount INT
DECLARE @Stepsize INT

SELECT @Counter = 40000
SELECT @MinCount = 0
SELECT @Stepsize = 1000

WHILE @Counter >= @MinCount  
BEGIN   
 SET @CounterText = CONVERT(VARCHAR(30),@Counter,23)
 RAISERROR (@CounterText, 10, 1) WITH NOWAIT   
 -- do useful stuff here
 SELECT @Counter = @Counter - @Stepsize  
END 

No comments: