有没有办法在 SQL Server 游标中使用参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6256599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to use parameters in a SQL Server cursor?
提问by Ascalonian
I have a parent-child relationship in the database. What I need to do is loop through the parent's query, and using the parent's primary key, got get its children. The issue I am having is that I need to use a parameterized cursor (pass in the key) to do this.
我在数据库中有父子关系。我需要做的是遍历父级的查询，并使用父级的主键，得到它的子级。我遇到的问题是我需要使用参数化游标（传入键）来执行此操作。
Is there such a thing in SQL Server or a trick to mimic this? I tried doing this, but it didn't work:
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
But it seems that it doesn't pick up me setting the @someKey.
但它似乎并没有让我设置@someKey。
Any help on this would be greatly appreciated. Thanks!
对此的任何帮助将不胜感激。谢谢！
UPDATE
更新
I should include more information as I made the example seem too simple. I have multiple @someKey values that I need to use. As mentioned before, I have a parent-child relationship and I can have up to 6 children. So I am getting a list of parents and it's respective columns and iterating through it. While in the WHILE-LOOP, I wanted to get the primary key from the parent and call another cursor to get the child information (different columns returned). So I would do multiple calls to the child cursor with different @someKey values set. Hope that makes sense.
我应该包含更多信息，因为我使示例看起来太简单了。我有多个需要使用的 @someKey 值。如前所述，我是亲子关系，最多可以有6个孩子。所以我得到了一个父母列表，它是各自的列并遍历它。在 WHILE-LOOP 中，我想从父级获取主键并调用另一个游标来获取子信息（返回不同的列）。所以我会用不同的@someKey 值设置多次调用子游标。希望这是有道理的。
采纳答案by John Kane
One thing you could try is using nested cursors. An example of this is on the bottom of the page titled: Using nested cursors to produce report output.
您可以尝试的一件事是使用嵌套游标。这方面的一个示例位于标题为：使用嵌套游标生成报告输出的页面底部。
回答by Ajith Kannan
What you need is 2 cursors - one for the parent and one for the child .Make sure the child cursor is DECLARED inside the LOOP not outside.it will not work if you declare outside.
您需要的是 2 个游标 - 一个用于父游标，一个用于子游标。确保子游标在 LOOP 内部而不是外部声明。如果您在外部声明，它将无法工作。
eg :
例如：
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 value=@value;
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
回答by Rob at TVSeries.com
In another place, someone suggested using a stored procedure (compiled SQL rather than an ad-hoc script) but that doesn't work either. Here's another MWE that shows the issue fairly clearly:
在另一个地方，有人建议使用存储过程（编译的 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];
It appears that the variable (and its value at the time) gets bound to the "declare ... cursor" rather than the open cursor.
似乎变量（及其当时的值）绑定到“声明...游标”而不是打开的游标。
回答by nwsmith
Here is how you can declare a cursor with dynamic SQL, using the 'EXEC()' function. Surprisingly this does work. For example:
下面是如何使用“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
回答by Remus Rusanu
If you want to iterate over a recursive hierarchy use CTEs, see Recursive Queries Using Common Table Expressions. You can declare your cursor over the recursive CTE, eg:
如果要使用 CTE 遍历递归层次结构，请参阅使用公用表表达式的递归查询。您可以在递归 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;
But most often the recursive CTEs can completely eliminate the need for a cursor.
但大多数情况下，递归 CTE 可以完全消除对游标的需求。
回答by Akhil
you need to set @someKey = 12345;before Cursor Declaration such as:
您需要@someKey = 12345;在 Cursor Declaration 之前进行设置，例如：
SET @someKey = 12345;
DECLARE main_curs
CURSOR FOR SELECT value FROM someTable where key = @someKey;
回答by Fosco
You seem to have the order of things wrong, and you're not actually doing anything inside the cursor?
你似乎把事情的顺序弄错了，你实际上并没有在游标内做任何事情？
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
