Thursday, April 28, 2011

How to Access Cursor Columns Without FETCH .. INTO

I'm using SQL Server to build stored procedures, and I'm using cursors to loop through a select statement

I'm defining the cursor as follow:

DECLARE @c_col1 varchar(max);
DECLARE @c_col2 varchar(max);

DECLARE c as CURSOR FOR 
SELECT col1, col2 
FROM table;

OPEN c;
FETCH NEXT FROM c INTO
@c_col1, @c_col2;

SELECT @c_col1, @c_col2;

Is there a way to access the columns of the cursor without a need to declare variables for each column and to use INTO in FETCH clause? In other words, is it possible to use:

DECLARE c as CURSOR FOR 
SELECT col1, col2 
FROM table;

OPEN c;
FETCH NEXT FROM c; 

SELECT c.col1, c.col2;
From stackoverflow
  • No, you have to do it that way if you want to store the values from the cursor in local variables instead of returning them back to the client.

    KM : from the OP's code, they are returning the values as a 1 row reslt set of two columns
  • if this is your entire porcedure (right from OP question):

    DECLARE @c_col1 varchar(max);
    DECLARE @c_col2 varchar(max);
    
    DECLARE c as CURSOR FOR 
    SELECT col1, col2 
    FROM table;
    
    OPEN c;
    FETCH NEXT FROM c INTO
    @c_col1, @c_col2;
    
    SELECT @c_col1, @c_col2;
    

    then you can just do the following to return a result set of the two columns, no cursor necessary:

    SELECT top 1 col1, col2 
    FROM table;
    

0 comments:

Post a Comment