在SQL Server 2008中的动态游标中面临的问题 [英] Facing Problem in dynamic cursor in sql server 2008

问题描述

declare @str nvarchar(1000)
declare @cstr nvarchar(2000)


DECLARE @c1 CURSOR
   set @str=N''SELECT EmpID FROM  Emp where '' + @whereqry 
    
 set @cstr = ''SET @c1  = CURSOR FOR '' + @str

 exec sp_executesql @cstr 
 open @c1
FETCH NEXT
FROM @c1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
........

FETCH NEXT
FROM @c1 INTO @id
END
CLOSE @c1
DEALLOCATE @c1

当我执行程序错误时出现

必须声明标量变量"@ c1".
变量``@ c1''当前没有分配游标.

When i execute procedure error comes

Must declare the scalar variable "@c1".
The variable ''@c1'' does not currently have a cursor allocated to it.

Can someone shed some light?

推荐答案

您不能(我相信)用这种方式来创建动态游标.您将需要在字符串中包含整个游标声明，然后执行sql.即

You cant (I believe) have a dynamic cursor this way. You would need to have the whole cursor declaration in a string and execute the sql. i.e.

declare @str nvarchar(1000)
declare @cstr nvarchar(2000)
 
set @str=N'SELECT EmpID FROM  Emp where ' + @whereqry 
set @cstr = 'DECLARE c1 CURSOR FOR ' + @str

exec sp_executesql @cstr 
open c1
FETCH NEXT
FROM c1 INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
........
 
FETCH NEXT
FROM c1 INTO @id
END
CLOSE c1
DEALLOCATE c1

参考: http://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure [

reference : http://stackoverflow.com/questions/1045880/using-a-cursor-with-dynamic-sql-in-a-stored-procedure[^]

这篇关于在SQL Server 2008中的动态游标中面临的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！