Create a Cursor in SQL Server
8/11/2006 1:50:47 PM
I have been learning a lot lately in the arena of T-SQL from working on Reporting Services and what-not. Cursors, at least for me, have been sort of a recurring theme in the creation of SQL Reports. They are actually pretty handy when it comes updating multiple tables with multiple subsets of data.
For your enjoyment I have provided a very simple use of a cursor. The cursor will select a subset of data and then use that data to update fields in a different table. This is aimed more towards the beginners. In no way do I claim to be a cursor ninja.
DECLARE @foo nvarchar(20)
DECLARE @bar money
DECLARE crsFB CURSOR FOR
(
-- select Values for @foo & @bar
SELECT
foo_column,
bar_column
FROM
table_name
)
OPEN crsFB
FETCH NEXT FROM crsFB INTO @foo, @bar
WHILE @@FETCH_STATUS = 0
BEGIN
-- update another table with @foo & @bar
UPDATE
server.db.tbl.owner.tablename
SET
something = @bar,
something = GetDate()
WHERE
this = @bar
FETCH NEXT FROM crsFB INTO @foo, @bar
END
CLOSE crsFB
DEALLOCATE crsFB
SQL
