有没有办法在SQL Server光标中使用参数？

Question

我在数据库中有一个父子关系。我需要做的是循环父母的查询，并使用父母的主键，得到它的孩子。我遇到的问题是我需要使用参数化游标（传入密钥）来执行此操作。

在SQL Server中是否存在这样的事情或者一个模仿它的诡计？我试过这样做，但它没有工作：

DECLARE @value VARCHAR(20); 
DECLARE @someKey NUMERIC(19,0); 

DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey; 

SET @someKey = 12345; 

OPEN main_curs 
FETCH NEXT FROM main_curs INTO @value; 
CLOSE main_curs 
DEALLOCATE main_curs 

但它似乎没有拿起我设置@someKey。

任何帮助，将不胜感激。谢谢！

UPDATE

我应该包括更多的信息，因为我所做的例子似乎太简单了。我有多个我需要使用的@someKey值。如前所述，我有一个亲子关系，我最多可以有6个孩子。所以我得到了一份家长名单，它是各自的专栏，并通过它迭代。在WHILE-LOOP中，我想从父项中获取主键并调用另一个光标来获取子项信息（返回不同的列）。所以我会用不同的@someKey值设置多个对子游标的调用。希望这是有道理的。

Answer 1

你可以尝试的一件事是使用nested cursors。一个例子是标题为：的页面底部使用嵌套游标生成报告输出。

Answer 2

需要之前设置@someKey = 12345;游标声明，如：

SET @someKey = 12345; 
DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey; 

Answer 3

你似乎有东西错了顺序，你没有真正做光标里面什么？

DECLARE @value VARCHAR(20); 
DECLARE @someKey NUMERIC(19,0); 

SET @someKey = 12345; --this has to be set before its used in cursor declaration 

DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey; 
OPEN main_curs 
FETCH NEXT FROM main_curs INTO @value;  -- first row is fetched 

WHILE @@FETCH_STATUS = 0  -- start the loop 
BEGIN 

-- do something here with @value 

FETCH NEXT FROM main_curs INTO @value; --fetch the next row 
END 

CLOSE main_curs 
DEALLOCATE main_curs 

Answer 4

如果您想遍历递归层次使用CTE，请参阅Recursive Queries Using Common Table Expressions。您可以在递归CTE上声明您的光标，例如：

create table test (
    id int not null identity(1,1) primary key, 
    parent_id int null, 
    data varchar (max)); 
go 

insert into test (parent_id, data) values 
    (null, 'root'), 
    (1, 'child 1'), 
    (1, 'child 2') , 
    (2, 'child of child 1'), 
    (4, 'child of child of child 1'); 
go 

declare @root int = 2; 

declare crs cursor for 
    with cte as (
     select id, parent_id, data 
     from test 
     where id = @root 
     union all 
     select t.id, t.parent_id, t.data 
     from test t 
      join cte c on t.parent_id = c.id) 
    select id, data from cte;  
open crs; 

declare @id int, @data varchar(max); 
fetch next from crs into @id, @data; 
while @@fetch_status = 0 
begin 
    print @data; 
    fetch next from crs into @id, @data; 
end 

close crs; 
deallocate crs; 

但是大多数情况下，递归CTE可以完全消除对游标的需要。

Answer 5

你需要的是2个游标 - 一个用于父级，另一个用于子级。确保子游标在LOOP内部不被DECLARED声明，如果你声明在外部，它将不起作用。

如：

DECLARE @value VARCHAR(20); 
DECLARE @someKey NUMERIC(19,0); 

DECLARE main_curs 
CURSOR FOR SELECT value FROM someTable where key = @someKey; 

SET @someKey = 12345; 

OPEN main_curs 
FETCH NEXT FROM main_curs INTO @value; 
while @@FETCH_STATUS = 0 
BEGIN 

DECLARE CHILD_CURS CURSOR FOR SELECT VALUE2 FROM CHILDTABLE WHERE [email protected]; 
open child_curs 
fetch next from child_curs into @x,@y 

close child_curs 
deallocate child_curs 

FETCH NEXT FROM main_curs INTO @value; 
END 

CLOSE main_curs 
DEALLOCATE main_curs 

Answer 6

在另外一个地方，一个人在使用存储过程（编译SQL，而不是一个特设的脚本）建议，但不工作的。这里的另一个MWE，显示问题相当明确：

/* Should print: 
dbNamein=master dbNameout=master 
dbNamein=model dbNameout=model 
dbNamein=msdb dbNameout=msdb 
*/ 
create procedure [TestParamsWithOpenCursorStmt] 
as 
begin 

    declare @dbNameIn [nvarchar](255) = N'tempdb', 
     @dbNameOut [nvarchar](255), 
     @fs [int]; 
    declare dbNames cursor for 
     select db.[name] from [master].[sys].[databases] db 
     where db.[name] = @dbNameIn; 
    while (@dbNameIn != N'msdb') begin 
     if @dbNameIn = N'tempdb' 
     set @dbNameIn = N'master' 
     else if @dbNameIn = N'master' 
     set @dbNameIn = N'model' 
     else if @dbNameIn = N'model' 
     set @dbNameIn = N'msdb'; 
     open dbNames; 
     fetch next from dbNames into @dbNameOut; 
     set @fs = @@fetch_status; 
     if @fs != 0 continue; 
     raiserror (N'dbNamein=%s dbNameout=%s', 0, 0, @dbNameIn, @dbNameOut) with nowait; 
     close dbNames; 
    end; 
    deallocate dbNames; 

end; 
go 

execute [TestParamsWithOpenCursorStmt]; 

看来，变量（和它的时间价值）被绑定到“申报...光标”，而不是打开的游标。

Answer 7

下面介绍如何使用'EXEC（）'函数声明带有动态SQL的游标。令人惊讶的是，这确实奏效。例如：

DECLARE @QuotedDatabase NVARCHAR(128) = QUOTENAME('ReportServer') 
DECLARE @ObjectID INT = 389576426 
DECLARE @ColumnName NVARCHAR(128) 
DECLARE @ColumnType NVARCHAR(128) 
DECLARE @DeclareColumnCursor NVARCHAR(4000) 
SET @DeclareColumnCursor = ' 
    DECLARE ColumnCursor CURSOR READ_ONLY FORWARD_ONLY FOR 
    SELECT c.Name, t.Name 
    FROM ' + @QuotedDatabase + '.sys.columns c 
    INNER JOIN ' + @QuotedDatabase + '.sys.types t 
    ON c.user_type_id = t.user_type_id 
    WHERE c.object_id = ' + CAST(@ObjectID AS NVARCHAR) + ' 
    ORDER BY column_id' 
EXEC(@DeclareColumnCursor) 
OPEN ColumnCursor 
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @ColumnType 
PRINT @ColumnName + ',' + @ColumnType 
CLOSE ColumnCursor 
DEALLOCATE ColumnCursor