sql-server - 重用具有不同参数值的 SQL Server 游标？

Question

我想知道当 @variable 的值发生变化时，是否可以重新使用带有 CLOSE 参数的查询游标( OPEN + @variable )。在我看来，它总是需要 CLOSE + DEALLOCATE + DECLARE + OPEN 才能使 @variable 的新值生效。也许没什么大不了的，但是我想知道 DEALLOCATE + DECLARE 是否可以在使用之间省略。

这里有完整的简单示例供您尝试:

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; 

这是线性化的示例，但当外部游标更改内部游标的查询参数时，问题也适用于嵌套游标。

Answer 1

一种方法是使用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