Après avoir lu cet article, vous apprendrez les bases d’un SQL dynamique, comment construire des instructions basées sur des valeurs de variables et comment exécuter des instructions construites à l’aide de sp_executesql et EXECUTE() à partir d’une procédure stockée.
Tous les exemples trouvés dans cette leçon sont basés sur Microsoft SQL Server Management Studio et les exemples de bases de données D’AdventureWorks et WideWorldImporters. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon guide, commencer à utiliser SQL Server.,
construire du SQL dynamique dans une procédure stockée.
beaucoup de SQL que nous écrivons est explicitement écrit dans la procédure stockée. C’est ce que nous appelons le SQL statique. Tel est nommé parce qu’il ne change pas. Une fois qu’il est écrit, cela signifie qu’il est gravé dans la pierre.
ci-Dessous est un exemple de SQL statique:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
Avez-vous remarqué qu’il y a deux déclarations ici? Chaque instruction renvoie un résumé des titres de travail pour une année de naissance d’employé spécifique. Si nous voulons ajouter plus d’années de naissance, nous devons ajouter plus de déclarations., Et si nous ne devions écrire la déclaration qu’une seule fois et être en mesure de changer l’année à la volée?
c’est là que le SQL dynamique entre en jeu.
dynamic SQL est un SQL créé et exécuté au moment de l’exécution. Cela semble compliqué, mais ce n’est vraiment pas le cas. au lieu d’avoir les instructions tapées directement dans la procédure stockée, les instructions SQL sont d’abord construites et définies dans des variables.
Le code de ces variables est alors exécutée. Maintenant, continuons avec notre exemple, voici le même code utilisant le SQL dynamique:
le SQL dynamique est surligné en vert., C’est le SQL qui est construit pour chaque @annéenaissance. Au fur et à mesure que le SQL est construit, il est stocké dans @statement. Il est ensuite exécuté en utilisant sp_executesql, que nous expliquerons ci-dessous.
Introduction à sp_executesql
Vous pouvez utiliser sp_executeslq pour exécuter le SQL transact stocké dans une variable. Le formulaire d’instruction est
EXECUTE sp_executesql @statement.
Au cas où vous vous poseriez la question, sp_executesql est une procédure stockée système. Les procédures stockées dans le système étendent la langue et fournissent plus de fonctionnalités à utiliser.,
Voici un exemple simple d’essayer:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Si vous exécutez ce code dans la fenêtre de requête, vous obtiendrez un résultat similaire comme ceci:
2018-01-24 18:49:30.143
Maintenant que vous avez vu comment sp_executeslq fonctionne, nous allons mettre en pratique. Supposons qu’on vous ait demandé d’écrire une procédure de magasin qui renvoie soit le LineTotal moyen, soit la somme de LineTotal par ProductID pour les produits expédiés en 2011.
votre patron préférerait que cela soit écrit comme une procédure stockée. La procédure stockée doit accepter un paramètre @ReturnAverage., Si vrai, alors vous retournerez la moyenne, sinon la somme.
bien sûr, vous pouvez écrire cela comme deux requêtes distinctes, comme indiqué dans le proc stocké suivant, mais ce ne serait pas très amusant, car ce serait trop de frappe et sujet aux erreurs!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
la mauvaise partie ici est qu’il y a beaucoup de code en double, que j’ai coloré en vert. Il n’y a pas beaucoup de code unique, mais qu’il y a, est de couleur rouge.
avec toute cette redondance, nous avons une excellente opportunité de montrer du SQL dynamique. Allons-y pour elle!,
ici, au lieu d’avoir deux versions complètes du SQL, L’une pour AVG, l’autre pour SUM, nous construisons la version demandée à la volée.
le SQL est construit et enregistré dans l’instruction variable@. Cette variable est construite en fonction de la valeur du paramètre @returnAverage. Si elle est définie sur 1, alors @function représente la moyenne; sinon, la somme.
Vous pouvez voir où le SQL est ensuite construit pour créer une instruction. Remarquez le code couleur. Il devrait correspondre à des parties similaires dans la version statique; cela devrait vous aider à faire une comparaison.,
débogage SQL dynamique
vous vous demandez peut-être à quoi ressemble le SQL au moment de l’exécution. Vous pouvez facilement inspecter le code à l’aide du débogueur:
exécutez la procédure stockée à l’aide de la commande run du débogueur, puis entrez dans le code
continuez à entrer dans le code jusqu’à ce que vous lisiez L’instruction Execute mise en évidence ci-dessous.
Une fois que vous avez atteint cette instruction, passez le curseur de la souris sur l’instruction @et, lorsque l’astuce s’affiche, sélectionnez visualiseur de texte.,
le débogueur est puissant et mérite d’être compris. Si vous encourage fortement en savoir plus à ce sujet ici.
utilisation de sp_executesql avec des paramètres
Vous pouvez utiliser sp_executesql pour référencer des paramètres dans votre instruction. Cela rend finalement votre code plus facile à lire et offre des avantages d’optimisation car l’instruction peut être compilée une fois et réutilisée plusieurs fois.
La déclaration prend la forme:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Donc, nous allons expliquer les morceaux.
- @statement est le SQL que nous souhaitons exécuter.,
- @parameterDefinition est une chaîne contenant une définition de tous les paramètres référencés dans @statement. Chaque paramètre et type trouvé @instruction est répertorié. Le nom et le type sont séparés par un espace. Plusieurs paramètres sont séparés par une virgule.
ensuite, nous définissons les valeurs des paramètres, en spécifiant les paramètres et la valeur souhaitée. Les paramètres sont listés dans l’ordre défini dans la chaîne @ parameterDefinition.
- @parm1 est le premier paramètre défini dans la chaîne @parameterDefinition. Value est la valeur à laquelle vous souhaitez la définir.,
- @ parm2, est le deuxième paramètre, si défini, comme déclaré dans @ parameterDefinition.
- etc…
Voici un exemple simple qui ajoute deux nombres, à essayer:
Les différentes parties de la déclaration sont de couleur:
- @instruction (vert) – avis, il comprend 2 paramètres: @a et @b. Aussi ces avis ne sont pas déclarées dans TSQL. Ils sont plutôt définis dans la définition du paramètre.
- @ parameterDefinition (Bleu) – chaque paramètre répertorié est défini comme type int.
- valeurs des paramètres (rouge) – nous définissons la valeur des paramètres ici.,
pour conclure, dans cet exemple, nous avons une instruction SQL exécutée dynamiquement qui ajoute deux paramètres.
ces paramètres sont définis comme des entiers. La valeur de chaque paramètre est définie dans la commande sp_executesql.
exemple utilisant sp_executesql avec les paramètres
prenons notre exemple précédent et étendons-le. Plutôt que de coder en dur la shipDate dans la requête comme nous l’avons fait, apportons cela en tant que paramètre. Cela rend la requête plus flexible et fonctionne avec des années autres que 2011.,
pour effectuer cette modification, nous allons ajouter un paramètre à notre procédure stockée, ainsi que la requête dynamique. Nous utiliserons la commande sp_executesql pour appeler la requête dynamique en utilisant ces paramètres.
la procédure stockée mise à jour avec les modifications est présentée ci-dessous. Le paramètre de procédure stockée est vert et le paramètre de requête dynamique rouge.
pour exécuter cela, appelez simplement le proc uspCalculateSalesSummaryDynamic2 à partir d’une fenêtre de requête en utilisant la commande suivante:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Si vous le faites, vous verrez ces résultats.,
Permettez-moi de vous montrer une simplification fine, combinons @shipDateYear et @shipDate en un seul paramètre. Nous éliminerons @shipDateYear de notre code. Cela facilite le suivi et la lecture:
notez que L’instruction EXECUTE est beaucoup plus simple, il n’est pas nécessaire d’affecter le paramètre D’instruction SQL @shipDateYear à la valeur du paramètre de procédure de magasin @shipDate.
cela rend la déclaration plus compacte et plus facile à lire., Le flux semble mieux lire, car vous n’avez pas besoin de faire mentalement des connexions entre les paramètres de procédure stockés et les paramètres SQL
exécutez dynamic SQL avec EXECUTE()
Vous pouvez également utiliser la commande EXEC ou EXECUTE pour exécuter dynamic SQL. Le format de cette commande est
EXECUTE (@statement)
Voici un exemple simple d’essayer:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
Il est important de joindre @énoncé dans la parenthèse. Si vous ne le faites pas, L’instruction EXECUTE prend @statement, et au lieu d’exécuter le SQL dynamique, elle pense que la valeur de la variable est le nom d’une procédure stockée., Vous obtiendrez l’erreur suivante:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
bien sûr, cela donne un excellent conseil! Si vous le souhaitez, vous pouvez utiliser des variables pour spécifier les procédures stockées à l’appel.
sp_executesql contre EXECUTE
vous vous demandez peut-être Pourquoi utiliser sp_executesql contre EXECUTE. Quelles sont les différence entre les deux?
Voici quelques raisons pour lesquelles Microsoft recommande l’utilisation de sp_executesql pour exécuter le SQL dynamique:
- Avec EXÉCUTER tous les paramètres doivent être convertis à partir de leur type natif de l’Unicode., Cela entrave la capacité de l’optimiseur à faire correspondre le SQL construit dynamiquement avec un plan préexistant.
- En utilisant sp_executesql, l’optimiseur reconnaît les paramètres du SQL dynamique, ce qui permet à l’optimiseur de faire correspondre plus facilement les plans.
- Il est plus facile de lire des requêtes paramétrées que de lire un tas de texte concaténé qui les incorpore.
- les requêtes paramétrées sont moins sujettes aux attaques par injection SQL.