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?
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
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
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.
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"....
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.
- Oracle EXÉCUTION IMMÉDIATE dans un curseur
- boucle à travers un curseur implicite (une "boucle 151900920" implicitement définit/ouvre/ferme le curseur!)
il y a un autre exemple que je voudrais partager avec vous
: D
http://www.sommarskog.se/dynamic_sql.html#cursor0
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
...