是否可以将游标变量传递给 sp_executesql？
尽管在将变量传递给 sp_executesql 时将变量声明为“cursor”类型，但我收到错误“操作数类型冲突：nvarchar 与游标不兼容”。
declare CURSOR_TO_PASS cursor for... --a simple select statement
--cursor opened, values obtained, etc...
declare @item nvarchar(5);
declare @seqno int;
--@item and @seqno populated
declare @sql nvarchar(400) = N'update MYTABLE set Survey' + cast(@seqno as nvarchar(2)) + N' = @itemvalue where current of @sc';
exec sp_executesql @sql, N'@itemvalue nvarchar(5), @sc cursor', @itemvalue = @item, @sc = CURSOR_TO_PASS;
我不知道出了什么问题，因为我已将 @sc 声明为游标，而 CURSOR_TO_PASS 是游标，我在调用 sp_executesql 时将其分配给 @sc 。那么，是否可以将游标传递给 sp_executesql？
Despite declaring a variable as type "cursor" when passing it to sp_executesql, I get the error "Operand type clash: nvarchar is incompatible with cursor".
declare CURSOR_TO_PASS cursor for... --a simple select statement
--cursor opened, values obtained, etc...
declare @item nvarchar(5);
declare @seqno int;
--@item and @seqno populated
declare @sql nvarchar(400) = N'update MYTABLE set Survey' + cast(@seqno as nvarchar(2)) + N' = @itemvalue where current of @sc';
exec sp_executesql @sql, N'@itemvalue nvarchar(5), @sc cursor', @itemvalue = @item, @sc = CURSOR_TO_PASS;
I don't know what's wrong, because I've declare @sc as a cursor, and CURSOR_TO_PASS is a cursor, which I'm assigning to @sc when calling sp_executesql. So, is it possible to pass a cursor to sp_executesql?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我在发布此内容后不久就找到了解决方案。事实上可以传递游标变量。只需一个中间步骤，您必须将光标分配给“光标变量”，如下所示： http://msdn.microsoft.com/en-us/library/ms190028.aspx“可以通过以下两种方法之一将游标与游标变量关联：”，两者其中需要一个初始的基本游标变量声明，例如“DECLARE @MyVariable CURSOR;”。
所以我将这些行添加到我的代码中：
然后我将
@sc = CURSOR_TO_PASS更改为@sc = @cursorvariable，并且工作正常。I figured out the solution shortly after posting this. It is in fact possible to pass a cursor variable. There's just an intermediate step required where you have to assign the cursor to a "cursor variable", as demonstrated here: http://msdn.microsoft.com/en-us/library/ms190028.aspx "A cursor can be associated with a cursor variable by either of two methods:", both of which require an initial, basic cursor variable declaration like "DECLARE @MyVariable CURSOR;".
So I added these lines to my code:
Then I changed
@sc = CURSOR_TO_PASSto@sc = @cursorvariable, and it worked fine.