在存储过程中使用带有动态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.

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

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?

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

7 个解决方案

#1

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.

游标只接受select语句,因此如果SQL确实需要是动态的,则声明游标是您正在执行的语句的一部分。为了使以下工作,您的服务器必须使用全局游标。

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

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.

如果需要避免使用全局游标,还可以将动态SQL的结果插入临时表,然后使用该表填充游标。

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

#2

This code is a very good example for a dynamic column with a cursor, since you cannot use '+' in @STATEMENT:

对于带有游标的动态列,此代码是一个非常好的示例,因为您无法在@STATEMENT中使用“+”:

ALTER PROCEDURE dbo.spTEST
AS
    SET NOCOUNT ON
    DECLARE @query NVARCHAR(4000) = N'' --DATA FILTER
    DECLARE @inputList NVARCHAR(4000) = ''
    DECLARE @field sysname = '' --COLUMN NAME
    DECLARE @my_cur CURSOR
    EXECUTE SP_EXECUTESQL
        N'SET @my_cur = CURSOR FAST_FORWARD FOR
            SELECT
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
            FROM
                dbo.vCard
            WHERE
                CASE @field
                    WHEN ''fn'' then fn
                    WHEN ''n_family_name'' then n_family_name
                END
                LIKE ''%''+@query+''%'';
            OPEN @my_cur;',
        N'@field sysname, @query NVARCHAR(4000), @my_cur CURSOR OUTPUT',
        @field = @field,
        @query = @query,
        @my_cur = @my_cur OUTPUT
    FETCH NEXT FROM @my_cur INTO @inputList
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @inputList
        FETCH NEXT FROM @my_cur INTO @inputList
    END
    RETURN

#3

Working with a non-relational database (IDMS anyone?) over an ODBC connection qualifies as one of those times where cursors and dynamic SQL seems the only route.

通过ODBC连接使用非关系数据库(IDMS任何人?)可以作为游标和动态SQL似乎是唯一路由的时间之一。

select * from a where a=1 and b in (1,2)

takes 45 minutes to respond while re-written to use keysets without the in clause will run in under 1 second:

需要45分钟才能响应,同时重新编写使用键集而不使用in子句将在1秒内运行:

select * from a where (a=1 and b=1)
union all
select * from a where (a=1 and b=2)

If the in statement for column B contains 1145 rows, using a cursor to create indidivudal statements and execute them as dynamic SQL is far faster than using the in clause. Silly hey?

如果列B的in语句包含1145行,则使用游标创建indidivudal语句并将其作为动态SQL执行,这比使用in子句要快得多。傻嘿?

And yes, there's no time in a relational database that cursor's should be used. I just can't believe I've come across an instance where a cursor loop is several magnitudes quicker.

是的,在关系数据库中没有时间应该使用游标。我简直无法相信我遇到了一个光标循环速度快几倍的实例。

#4

First off, avoid using a cursor if at all possible. Here are some resources for rooting it out when it seems you can't do without:

首先,尽可能避免使用光标。以下是一些资源,用于在看起来你不能没有时将其生根:

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

必须有15种方法可以丢失你的游标......第1部分,简介

Row-By-Row Processing Without Cursor

没有游标的逐行处理

That said, though, you may be stuck with one after all--I don't know enough from your question to be sure that either of those apply. If that's the case, you've got a different problem--the select statement for your cursor must be an actual SELECT statement, not an EXECUTE statement. You're stuck.

虽然如此,毕竟你可能会被困在一个 - 我不清楚你的问题,以确保其中任何一个适用。如果是这种情况,则会遇到另一个问题 - 游标的select语句必须是实际的SELECT语句,而不是EXECUTE语句。你被困住了。

But see the answer from cmsjr (which came in while I was writing) about using a temp table. I'd avoid global cursors even more than "plain" ones....

但是请参阅cmsjr(在我写作时出现)关于使用临时表的答案。我会避免使用全局游标,而不仅仅是“普通游标”....

#5

After recently switching from Oracle to SQL Server (employer preference), I notice cursor support in SQL Server is lagging. Cursors are not always evil, sometimes required, sometimes much faster, and sometimes cleaner than trying to tune a complex query by re-arranging or adding optimization hints. The "cursors are evil" opinion is much more prominent in the SQL Server community.

在最近从Oracle切换到SQL Server(雇主偏好)之后,我注意到SQL Server中的游标支持滞后。游标并不总是邪恶的,有时需要,有时要快得多,有时比通过重新安排或添加优化提示来调整复杂查询更清晰。 “游标是邪恶的”意见在SQL Server社区中更为突出。

So I guess this answer is to switch to Oracle or give MS a clue.

所以我想这个答案是切换到Oracle或给MS一个线索。

Oracle EXECUTE IMMEDIATE into a cursor

Oracle EXECUTE IMMEDIATE到游标中

Loop through an implicit cursor (a for loop implicitly defines/opens/closes the cursor!)

循环一个隐式游标(for循环隐式定义/打开/关闭游标!)

#6

There is another example which I would like to share with you
:D http://www.sommarskog.se/dynamic_sql.html#cursor0

我还想与您分享另一个例子:D http://www.sommarskog.se/dynamic_sql.html#cursor0

#7

this code can be useful for you.

这段代码对你有用。

example of cursor use in sql server

在sql server中使用游标的示例

DECLARE sampleCursor CURSOR FOR 
      SELECT K.Id FROM TableA K WHERE ....;
OPEN sampleCursor
FETCH NEXT FROM sampleCursor INTO @Id
WHILE @@FETCH_STATUS <> -1
BEGIN

UPDATE TableB
   SET 
      ...

#1