get multiple values in sql server cursor

Question

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

Answer 1

SQL Server cursor: This SQL database cursor is referred to the set of TSQL logic which helps to traverse over different row.SQL server cursor is used for different purposes like Administration of database like taking database backups, or to perform any modification or maybe to perform some kind of Extract transform or load process.

Let’s have a look at how we fetch multiple values in the SQL server cursor:

DECLARE cursor_demo CURSOR FOR SELECT name, age, company FROM student_interns; 

DECLARE @goodName VARCHAR(256);

DECLARE @goodAge INT;

DECLARE @companyselected VARCHAR(40);

OPEN cursor_demo;

FETCH NEXT FROM db_cursor INTO @goodName, @goodAge, @companyselected;

WHILE @STATUS_COUNT = 0  

BEGIN  

FETCH NEXT FROM cursor_demo INTO  @goodName, @goodAge, @companyselected;

END;

CLOSE cursor_demo;

DEALLOCATE cursor_demo;

Answer 2

In SQL Server, you can retrieve multiple columns using a cursor by including them in the cursor definition through the SELECT statement. For this purpose, follow the following procedure

DECLARE @col1 datatype, @col2 datatype, ...;  -- Declaring variables 

DECLARE myCursor CURSOR FOR

SELECT col1, col2, ...

FROM table_name;

OPEN myCursor;

FETCH NEXT FROM myCursor INTO @col1, @col2, ...;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- Process each row

    PRINT @col1;

    PRINT @col2;

   

    FETCH NEXT FROM myCursor INTO @col1, @col2, ...;

END;

CLOSE myCursor;

DEALLOCATE myCursor;

Explanation

Declare and Define Variables: Create variables like @col1, @col2 to store values to be retried by cursor. Now specify multiple columns through DECLARE myCursor CURSOR FOR with a SELECT statement.

Fetch the rows by using FETCH NEXT FROM myCursor INTO

Get the value of each row assigned to variables, loop through While processing all the rows

Now Close and Deallocate the cursor freeing up any resources. 

 

Answer 3

You cannot directly access columns from the cursor like db_cursor.name

You need to apply the FETCH statement to assign the cursor values to variables,

One at a time 

 

Let’s see how we can modify the approach:

DECLARE @myNAME  NVARCHAR(50), @myAge INT, @myFavoriteColor NVARCHAR(50)

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;

WHILE @@FETCH_STATUS = 0

BEGIN

            --

 

        FETCH NEXT FROM db_cursor INTO @myName, @myAge, @mFavoriteColor;

 

END

CLOSE db_cursor;

DEALLOCATE db_cursor;

Using this approach, you will assign the cursor’s values to variables in each FETCH statement and then you can use those variables inside the loop