Tuesday, September 23, 2008

SQL Store Procedure CURSOR

I just learnt stored procedure with cursor pointer.

Here's an example:
BEGIN
DECLARE @MyID AS BIGINT
DECLARE MyIDCursor CURSOR FOR
SELECT ID FROM MyTable
OPEN MyIDCursor

FETCH NEXT FROM MyIDCursor INTO @MyID
WHILE @@FETCH_STATUS=0 -- this is necessary to loop
BEGIN
-- print it out
print '@MyID'
print @MyID
-- to assign to the next value, without this, it will loops forever!
FETCH NEXT FROM MyIDCursor INTO @MyID
END

CLOSE MyIDCursor
DEALLOCATE MyIDCursor
END
Actually this method is not recommended, look here.

No comments: