Wednesday, December 30, 2009

Looping in stored procedure

Problem
How to loop through the select result in a stored procedure

Solution
DECLARE @MyId int
DECLARE Scroller SCROLL CURSOR
FOR SELECT MyId FROM MyTable

OPEN Scroller
FETCH NEXT FROM Scroller INTO @MyId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO MyTable2 (ReferenceId) VALUES (@MyId)
FETCH NEXT FROM Scroller INTO @MyId
END

CLOSE Scroller
DEALLOCATE Scroller