kingrichard2005 kingrichard2005 - 4 months ago 12
SQL Question

Get Multiple Values in SQL Server Cursor

I have a cursor containing several columns from the row it brings back that I would like to process at once. I notice most of the examples I've seeing on how to use cursors show them assigning a particular column from the cursor to a scalar value one at a time, then moving to the next row,

e.g.

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
--Do Stuff with @name scalar value, then get next row from cursor

FETCH NEXT FROM db_cursor INTO @name
END


What I want to know is if it's possible to do something like the following:

OPEN db_cursor
FETCH NEXT FROM db_cursor;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @myName = db_cursor.name;
SET @myAge = db_cursor.age;
SET @myFavoriteColor = db_cursor.favoriteColor;
--Do stuff with scalar values

FETCH NEXT FROM db_cursor;
END


Help is always appreciated.

Answer

This should work:

DECLARE db_cursor CURSOR FOR SELECT name, age, color FROM table; 
DECLARE @myName VARCHAR(256);
DECLARE @myAge INT;
DECLARE @myFavoriteColor VARCHAR(40);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
WHILE @@FETCH_STATUS = 0  
BEGIN  

       --Do stuff with scalar values

       FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Comments