在存储过程中使用动态SQL游标在存储过程中使用动态SQL游标(Using a cursor with

我有我在存储过程中创建一个动态的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

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

Answer 1:

光标将只接受一个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

如果你需要避免使用游标全球，您也可以将您的动态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

Answer 2:

这个代码是利用光标动态列一个很好的例子，因为你不能在@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

Answer 3:

与非关系型数据库（IDMS人？）通过ODBC连接工作资格作为那个时代哪里游标和动态SQL似乎唯一的途径之一。

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

需要45分钟，同时重新写入到使用密钥集，而不在子句将运行在不到1秒钟到响应：

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

如果在声明中列B包含1145行，用光标创建indidivudal语句并执行动态SQL远比条款使用得更快。 傻哎？

是的，还有应使用游标的关系数据库中的任何时间。 我简直不能相信我遇到这样的情况：一个游标循环是几个数量级速度更快。

Answer 4:

首先，避免使用游标，如果在所有可能的。 这里有一些资源铲除它出来的时候，似乎你不能没有：

必须有15种方法失去你的光标...第1部分，介绍

行到行的处理没有光标

这就是说，虽然，你可能会坚持一个毕竟 - 我不知道从你的问题，足以确保这类原因申请。 如果是这样的话，你已经有了一个不同的问题-你的光标选择语句必须是实际的 SELECT语句，而不是EXECUTE语句。 你就完蛋了。

但看到cmsjr答案（这进来，而我写的）有关使用临时表。 我会避免全球光标不是“普通”的人更....

Answer 5:

最近从甲骨文切换到SQL Server（雇主偏好）后，我注意到在SQL Server游标支持滞后。 光标并不总是坏事，有时需要，有时比通过重新排列或添加优化提示试图调整复杂的查询速度更快，有时更清洁。 在“游标是邪恶的”意见是在SQL Server社区更为突出。

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

• 甲骨文EXECUTE IMMEDIATE到游标
• 通过隐式游标循环 （一个for循环隐含地定义/打开/关闭光标！）

Answer 6:

还有，我想与大家分享另一个例子
：d http://www.sommarskog.se/dynamic_sql.html#cursor0

Answer 7:

在SQL Server的另一种方法是你所有的动态查询的做成表变量的存储过程，然后使用游标来查询并处理。 至于可怕光标辩论:)，我所看到的研究显示，在某些情况下，光标实际上可以更快，如果正确设置。 我用他们自己时所需的查询是太复杂了，或者只是不从人的角度（对我来说））成为可能。

Answer 8:

这个代码可以对你有用。

例如在SQL服务器的光标的使用

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 
      ...

文章来源: Using a cursor with dynamic SQL in a stored procedure