Utilisation d'un curseur avec le SQL dynamique dans une procédure stockée

j'ai une déclaration SQL dynamique que j'ai créée dans une procédure stockée. Je dois itérer les résultats en utilisant un curseur. Je vais avoir un moment difficile de déterminer la bonne syntaxe. Voici ce que je suis en train de faire.

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

Quelle est la bonne façon de faire cela?

39
demandé sur Leniel Maccaferri 2009-06-25 23:51:49

7 réponses

un curseur n'acceptera qu'une instruction select, donc si le SQL a vraiment besoin d'être dynamique, faites du curseur declare une partie de l'instruction que vous exécutez. Pour que votre serveur fonctionne, vous devrez utiliser des curseurs globaux.

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

si vous devez éviter d'utiliser les curseurs globaux, vous pouvez également insérer les résultats de votre SQL dynamique dans une table temporaire, puis utiliser cette table pour remplir votre curseur.

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
98
répondu cmsjr 2011-06-01 01:29:33

ce code est un très bon exemple pour une colonne dynamique avec un curseur, car vous ne pouvez pas utiliser ' + 'dans la déclaration@:

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
15
répondu SMHMayboudi 2011-07-08 18:30:49

travaillant avec une base de données non relationnelle (IDMS anyone?) sur une connexion ODBC se qualifie comme l'une de ces fois où Curseurs et dynamique SQL semble la seule voie.

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

prend 45 minutes pour répondre alors qu'il est réécrit pour utiliser des touches sans la clause in s'exécute en moins d'une seconde:

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

si l'instruction in pour la colonne B contient 1145 lignes, en utilisant un curseur pour créer des instructions indidivudal et les exécuter comme dynamique SQL est beaucoup plus rapide que L'utilisation de la clause in. Idiot hein?

et oui, il n'y a pas de temps dans une base de données relationnelle où le curseur devrait être utilisé. Je n'arrive pas à croire que je suis tombé sur une instance où une boucle de curseur est plusieurs magnitudes plus rapide.

4
répondu Twelfth 2011-06-01 01:34:03

tout d'Abord, évitez d'utiliser un curseur, si possible. Voici quelques ressources pour l'enracinement il semble que vous ne pouvez pas faire sans:

Il Doit Y Avoir 15 Façons De Perdre Ses Curseurs... partie 1, Introduction

Traitement Ligne Par Ligne Sans Curseur

qui dit, cependant, vous pouvez être coincé avec un après tout--Je ne sais pas assez de votre question pour être sûr que l'une de celles qui s'appliquent. Si c'est le cas, vous avez un problème différent--l'instruction select pour votre curseur doit être une instruction SELECT actual , et non une instruction EXECUTE. Vous êtes coincé.

Mais voir la réponse de cmsjr (qui est venu pendant que j'écrivais) sur l'utilisation d'une table temporaire. J'éviterais global curseurs encore plus que" simples"....

3
répondu RolandTumble 2009-06-25 20:12:42

après avoir récemment changé D'Oracle à SQL Server (préférence de l'employeur), je remarque que le support du curseur dans SQL Server est en retard. Les curseurs ne sont pas toujours mauvais, parfois nécessaires, parfois beaucoup plus rapides, et parfois plus propres que d'essayer d'accorder une requête complexe en réarrangeant ou en ajoutant des conseils d'optimisation. L'opinion" les curseurs sont mauvais " est beaucoup plus importante dans la communauté SQL Server.

donc je suppose que cette réponse est de passer à Oracle ou de donner à MS un indice.

1
répondu crokusek 2017-05-23 12:02:45

il y a un autre exemple que je voudrais partager avec vous

: D http://www.sommarskog.se/dynamic_sql.html#cursor0

1
répondu SMHMayboudi 2011-07-07 15:19:33

ce code peut vous être utile.

exemple d'utilisation du curseur dans le serveur 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 
      ...
-3
répondu carlito 2013-05-17 16:22:29