CODEWITHSUNDEEP
sqlsql-serverdatabase-cursor
ala
ala

Reputation: 7290

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;

Upvotes: 9

Views: 17221

Answers (2)

KM.
KM.

Reputation: 103597

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;

Upvotes: 0

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 4

Related Questions