How to Access Cursor Columns Without FETCH .. INTO

Question

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;

Why do you need to use cursors? You should really try and find a set based alternative using SQL as this is what databases are good at processing. Often cursor code is much much slower than the equivalent SQL. — pjp
– pjp, Commented Aug 19, 2009 at 8:27
@pjp, sometimes cursors are useful to run some complex logic on a row-by-row basis, often by calling a stored procedure for each row returned. Also, if you need to do something like delete a few million rows, a single set-based query can lock up your database for a long time. Deleting one row at a time with a cursor takes a lot longer but allows the database to breathe while it's happening. — Eric Z Beard
– Eric Z Beard, Commented Aug 19, 2009 at 10:11
you can loop and process data, without a cursor. You can get massive performance improvements replacing cursors with set based operations. However, I have replaced the use of cursors, but still looped (because it was really necessary) and have still seen big performance gains. — KM.
– KM., Commented Aug 19, 2009 at 11:52
I'm not sure what you are after, but your code is not typical, and you certainly do not have to do it that way, as the selected answer says. — KM.
– KM., Commented Aug 21, 2009 at 12:50
@Eric Z Beard, this exactly when I would never use a cursor. I'd process in batches using a while loop. Much faster to delete 10000 records at a time in a loop than run row-by-agonizing-row in a cursor. — HLGEM
– HLGEM, Commented Nov 22, 2010 at 18:42

Eric Z Beard · Accepted Answer · 2009-08-19 10:09:12Z

4

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.

answered Aug 19, 2009 at 10:09

Eric Z Beard

38.5k28 gold badges102 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KM. Over a year ago

from the OP's code, they are returning the values as a 1 row reslt set of two columns

KM. · Accepted Answer · 2009-08-19 11:58:41Z

0

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;

edited Aug 19, 2009 at 11:58

answered Aug 19, 2009 at 11:49

KM.

104k34 gold badges183 silver badges214 bronze badges

Collectives™ on Stack Overflow

How to Access Cursor Columns Without FETCH .. INTO

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related