在存储过程中使用带有动态 SQL 的游标 [英] Using a cursor with dynamic SQL in a stored procedure

问题描述

我有一个在存储过程中创建的动态 SQL 语句.我需要使用游标迭代结果.我很难找出正确的语法.这就是我正在做的事情.

I have a dynamic SQL statement I've created in a stored procedure. I need to iterate over the results using a cursor. I'm having a hard time figuring out the right syntax. Here's what I'm doing.

SELECT @SQLStatement = 'SELECT userId FROM users'

DECLARE @UserId

DECLARE users_cursor CURSOR FOR
EXECUTE @SQLStatment --Fails here. Doesn't like this

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

这样做的正确方法是什么?

What's the right way to do this?

推荐答案

游标只会接受一个 select 语句，所以如果 SQL 真的需要是动态的，请将声明游标作为您正在执行的语句的一部分.为了使下面的工作正常运行，您的服务器必须使用全局游标.

A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors.

Declare @UserID varchar(100)
declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare  users_cursor CURSOR FOR SELECT userId FROM users'

exec sp_executesql @sqlstatement


OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN
Print @UserID
EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

如果您需要避免使用全局游标，您也可以将动态 SQL 的结果插入到一个临时表中，然后使用该表来填充您的游标.

If you need to avoid using the global cursors, you could also insert the results of your dynamic SQL into a temporary table, and then use that table to populate your cursor.

Declare @UserID varchar(100)
create table #users (UserID varchar(100))

declare @sqlstatement nvarchar(4000)
set @sqlstatement = 'Insert into #users (userID) SELECT userId FROM users'
exec(@sqlstatement)

declare users_cursor cursor for Select UserId from #Users
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @UserId

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC asp_DoSomethingStoredProc @UserId

FETCH NEXT FROM users_cursor
INTO @UserId

END
CLOSE users_cursor
DEALLOCATE users_cursor

drop table #users

这篇关于在存储过程中使用带有动态 SQL 的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！