开发者

How can I set a cursor to a dynamically created SQL query in stored procedure

开发者 https://www.devze.com 2023-03-02 10:53 出处:网络
I want to create a dynamic command using @sqlQuery variable.I\'ve also declared a cursor (example: @myCursor).How can I \"SET @myCursor = CURSOR FO开发者_运维百科R @sqlQuery\".The syntax I just noted

I want to create a dynamic command using @sqlQuery variable. I've also declared a cursor (example: @myCursor). How can I "SET @myCursor = CURSOR FO开发者_运维百科R @sqlQuery". The syntax I just noted doesn't work. I am using SQL 2000.


You should take a look at The Curse and Blessings of Dynamic SQL


You can do it using sp_executesql. Just be sure to open the cursor within the dynamic SQL.

CREATE PROCEDURE OpenCursor (@query nvarchar(max), @cur cursor VARYING OUTPUT)
AS
    DECLARE @sql nvarchar(max)
    SET @sql = N'SET @cur = CURSOR STATIC FOR ' + @query + '; OPEN @cur'
    EXEC sp_executesql @sql, N'@cur cursor OUTPUT', @cur OUTPUT
GO

DECLARE @cur cursor
EXEC OpenCursor 'SELECT * FROM sysobjects', @cur OUTPUT
FETCH NEXT FROM @cur
0

精彩评论

暂无评论...
验证码 换一张
取 消