Requête SQL Dynamique.

Bonjour.

Débutant en SQL dynamique, Je n'arrive pas à résoudre le problème généré par la requête suivante. Je suis sur SQL SERVER 2005 SP3.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

DEClARE @VAR1 INT, @VAR2 VARCHAR(1000), @VAR3 VARCHAR(1000), @VAR4 INT, @VAR5 VARCHAR(1000), @VAR6 VARCHAR(1000)   INSERT INTO TmpOldDevis (IDOldDEVIS) Select Devis.IDDevis From Devis where Devis.IDPropositions = 36524   INSERT INTO TmpNewDEVIS (IDNewDEVIS) Select Devis.IDDevis From Devis where Devis.IDPropositions = 37899     SET @VAR1 = 1 BEGIN TRAN WHILE @VAR1 <= (Select Count(*) From TmpOldDevis) BEGIN SET @VAR2 ='SELECT (IDOldDEVIS) FROM TmpOldDevis WHERE Temp_IDOldDEVIS ='+CAST(@VAR1 as VARCHAR(3)) EXECUTE(@VAR2); SET @VAR3= 'INSERT INTO TmpLignesMateriels(IDLignesMateriels,IDDevis,IDProduits,Designation,Quantité,PrixNetHT,TotalHT,PrixAchatHT,NumeroLigne,IDTVA, RemiseAnnuelle,RemiseComplementaire,PrixNetHT_RA,TotalHT_RA) SELECT IDLignesMateriels,IDDevis,IDProduits,Designation,Quantité,PrixNetHT,TotalHT,PrixAchatHT,NumeroLigne,IDTVA, RemiseAnnuelle,RemiseComplementaire,PrixNetHT_RA,TotalHT_RA FROM LignesMateriels Inner Join TmpOldDevis On LignesMateriels.IDDevis = TmpOldDevis.IDOldDEVIS WHERE LignesMateriels.IDDevis = TmpOldDevis.IDOldDEVIS and TmpOldDevis.Temp_IDOldDEVIS='+CAST(@VAR1 as VARCHAR(3)) /*+CAST(159021 as VARCHAR(6))159021' +CAST(159021 as VARCHAR(6))+*//*TmpOldDevis.IDOldDEVIS and TmpOldDevis.Temp_IDOldDEVIS='+CAST(@VAR1 as VARCHAR(3)) */ print @var3 EXECUTE(@VAR3); SET @VAR5 ='(SELECT (IDNewDEVIS) FROM TmpNewDEVIS WHERE Temp_IDNewDEVIS = '+CAST(@VAR1 as VARCHAR(3))+')' EXECUTE(@VAR5);   SET @VAR6 ='INSERT INTO LignesMateriels(IDDevis,IDProduits,Designation,Quantité,PrixNetHT,TotalHT,PrixAchatHT,NumeroLigne,IDTVA, RemiseAnnuelle,RemiseComplementaire,PrixNetHT_RA,TotalHT_RA) SELECT '+@VAR5+',IDProduits,Designation,Quantité,PrixNetHT,TotalHT,PrixAchatHT,NumeroLigne,IDTVA,RemiseAnnuelle,RemiseComplementaire,PrixNetHT_RA,TotalHT_RA FROM TmpLignesMateriels Inner Join TmpOldDevis On TmpLignesMateriels.IDDevis = TmpOldDevis.IDOldDEVIS WHERE TmpOldDevis.IDOldDEVIS = TmpOldDevis.IDOldDEVIS and TmpOldDevis.Temp_IDOldDEVIS='+CAST(@VAR1 as VARCHAR(3))     EXECUTE(@VAR6); SET @var1 = @var1+1 END COMMIT TRAN

---

J'ai systématiquement le message suivant "Le nom de la variable @P1 a déjà déclaré. les noms des variables doivent être unique dans un lot de requêtes ou une procédure stockée".
SQL State 42000
SQL ERROR 134.

Par avance, merci de votre aide.

Bonjour,

Est-ce que ce code fait parti d'une procédure stockée ? Si oui as-tu la définition entière de la procédure + bout de code que tu as mis ?

++

Bonjour,

En sus de ce que vous demande Mikedavem, vous pouvez rendre votre code vraiment dynamique en utilisant la procédure stockée sp_executesql :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134

DECLARE @sql nvarchar(max) , @IDNewDEVIS int   INSERT INTO TmpOldDevis ( IDOldDEVIS ) SELECT IDDevis FROM dbo.Devis WHERE Devis.IDPropositions = 36524   INSERT INTO TmpNewDEVIS ( IDNewDEVIS ) SELECT IDDevis FROM dbo.Devis WHERE IDPropositions = 37899   SELECT @old_devis_count = row_count FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.TmpOldDevis') AND index_id BETWEEN 0 AND 1   WHILE @old_devis_count >= 1 BEGIN BEGIN TRAN SET @sql = 'SELECT IDOldDEVIS FROM dbo.TmpOldDevis WHERE Temp_IDOldDEVIS = @_old_devis_count'   EXEC sp_executesql @sql , N'@_old_devis_count int' , @_old_devis_count = @old_devis_count   SET @sql = 'INSERT INTO dbo.TmpLignesMateriels ( IDLignesMateriels , IDDevis , IDProduits , Designation , Quantité , PrixNetHT , TotalHT , PrixAchatHT , NumeroLigne , IDTVA , RemiseAnnuelle , RemiseComplementaire , PrixNetHT_RA , TotalHT_RA ) SELECT IDLignesMateriels , IDDevis , IDProduits , Designation , Quantité , PrixNetHT , TotalHT , PrixAchatHT , NumeroLigne , IDTVA , RemiseAnnuelle , RemiseComplementaire , PrixNetHT_RA , TotalHT_RA FROM dbo.LignesMateriels AS LM INNER JOIN dbo.TmpOldDevis AS D ON LM.IDDevis = D.IDOldDEVIS WHERE TmpOldDevis.Temp_IDOldDEVIS = @_old_devis_count'   EXEC sp_executesql @sql , N'@_old_devis_count int' , @_old_devis_count = @old_devis_count   SET @sql = 'SELECT @IDNewDEVIS = IDNewDEVIS FROM dbo.TmpNewDEVIS WHERE Temp_IDNewDEVIS = @_old_devis_count'   EXEC sp_executesql @sql , N'@_old_devis_count int, @_IDNewDEVIS OUTPUT' , @_old_devis_count = @old_devis_count , @_IDNewDEVIS = @IDNewDEVIS OUTPUT   SET @sql = 'INSERT INTO dbo.LignesMateriels ( IDDevis , IDProduits , Designation , Quantité , PrixNetHT , TotalHT , PrixAchatHT , NumeroLigne , IDTVA , RemiseAnnuelle , RemiseComplementaire , PrixNetHT_RA , TotalHT_RA ) SELECT @_IDNewDEVIS , IDProduits , Designation , Quantité , PrixNetHT , TotalHT , PrixAchatHT , NumeroLigne , IDTVA , RemiseAnnuelle , RemiseComplementaire , PrixNetHT_RA ,TotalHT_RA FROM dbo.TmpLignesMateriels AS LM INNER JOIN dbo.TmpOldDevis AS D ON LM.IDDevis = D.IDOldDEVIS WHERE D.Temp_IDOldDEVIS = @_old_devis_count'   EXEC sp_executesql @sql , N'@_old_devis_count int, @_IDNewDEVIS int' , @_old_devis_count = @old_devis_count , @_IDNewDEVIS = @IDNewDEVIS   SET @var1 = @var1 - 1 COMMIT TRAN END

---

De cette façon, il est possible de conserver des requêtes paramétrées, et donc de ne pas générer un plan d'exécution à chaque fois que les valeurs des filtres changent : le plan est alors réutilisé, plutôt que d'être calculé à chaque exécution.
En effet, lorsqu'on soumet une chaîne de requête à SQL Server, il en calcule un hash, et cherche dans le cache de plans s'il existe une requête qui a le même hash. En conséquence, le simple fait de rajouter un espace n'importe où dans le texte de la requête, ou la casse d'une lettre, ..., fait varier la valeur du hash, et entraîne donc la création d'un nouveau plan.
Au final, le cache de plans contient donc de nombreux plans pour une même requête (aux valeurs des "paramètres" près), qui pourrissent la richesse et la ré-utilisabilité de celui-ci ;)

Vous avez aussi laissé deux redondances que j'ai enlevées :

- WHERE TmpOldDevis.IDOldDEVIS = TmpOldDevis.IDOldDEVIS
- On LignesMateriels.IDDevis = TmpOldDevis.IDOldDEVIS WHERE LignesMateriels.IDDevis = TmpOldDevis.IDOldDEVIS

Enfin il faut faire en sorte d'avoir des transactions courtes.
Donc le WHILE ne doit pas être à l'intérieur d'une transaction explicite : c'est plutôt l'inverse ;)

@++ ;)