Reuse SQL Server cursor with different parameter value?

Question

I wonder whether cursor for query with @variable parameter can be re-used (CLOSE + OPEN) when value of the @variable changes. To me it looks that it always needs CLOSE + DEALLOCATE + DECLARE + OPEN to take new value of the @variable into effect. Perhaps no big deal, but I wanted to know whether DEALLOCATE + DECLARE can be left out between uses.

Here you have complete simple example to try it out:

DECLARE @ta TABLE (a int);
INSERT INTO @ta (a) VALUES (1),(2),(4),(8),(16),(32),(64);
---------
DECLARE @current_a int;
DECLARE @threshold int = 12;
DECLARE crs1 CURSOR FOR SELECT a FROM @ta WHERE a < @threshold;

--- first cursor use
OPEN crs1;

    FETCH NEXT FROM crs1 INTO @current_a;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @threshold, @current_a
            FETCH NEXT FROM crs1 INTO @current_a;
        END;    

CLOSE crs1;
DEALLOCATE crs1; -- can this be left out?

SET @threshold = 3;
DECLARE crs1 CURSOR FOR SELECT a FROM @ta WHERE a < @threshold;  -- can this be left out?

--- second cursor use
OPEN crs1;

    FETCH NEXT FROM crs1 INTO @current_a;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @threshold, @current_a
            FETCH NEXT FROM crs1 INTO @current_a;
        END;    

CLOSE crs1;
DEALLOCATE crs1;

This was linearized example, but the question applies also to nested cursors when outer cursor changes the query parameter of inner cursor.

Do you want something like cursor variable? Check demo — Lukasz Szozda
– Lukasz Szozda, Commented Apr 1, 2016 at 11:07
@lad2025 – yeah, I did not know cursor variables can do the job here. Please add this as answer and I'll accept it. — miroxlav
– miroxlav, Commented Apr 2, 2016 at 18:55

Lukasz Szozda · Accepted Answer · 2016-04-03 09:49:35Z

One way is to use cursor variable:

DECLARE 
{ 
    { @local_variable [AS] data_type  | [ = value ] }
  | { @cursor_variable_name CURSOR }
@cursor_variable_name

Is the name of a cursor variable. Cursor variable names must begin with an at (@) sign and conform to the rules for identifiers.

CURSOR

Specifies that the variable is a local cursor variable.

A cursor variable:

Can be the target of either a cursor type or another cursor variable. For more information, see SET @local_variable.

Can be referenced as the target of an output cursor parameter in an EXECUTE statement if the cursor variable does not have a cursor currently assigned to it.

Should be regarded as a pointer to the cursor.

DECLARE crs1 CURSOR FOR SELECT a FROM @ta WHERE a < @threshold;
-- could be changed to
DECLARE @crs1 CURSOR;
SET @crs1 = CURSOR FOR SELECT a FROM @ta WHERE a < @threshold;

LiveDemo

Collectives™ on Stack Overflow

Reuse SQL Server cursor with different parameter value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related