SQL dynamique – SQL Server T-SQL – Bien choisir son serveur d impression

[bzkshopping keyword= »Minecraft » count= »8″ template= »grid »]

Exécutez ce qui suit Exemples de scripts Microsoft SQL Server T-SQL dans l'éditeur de requêtes Management Studio pour illustrer la création et l'exécution de requêtes SQL dynamiques puissantes et flexibles. La requête SQL dynamique est exécutée dans un processus enfant. Article sur l'attaque par injection SQL via Dynamic SQL: Injection SQL et SQL dynamique. ———— – Dynamic SQL 101 – SYNTAXE RAPIDE – Exécution / exécution T-SQL – SQL Server sp_executeSQL– sp_exeduteSQL présente des avantages par rapport à EXEC y compris la performance (réutilisation du plan d'exécution) ———— UTILISER AdventureWorks2008; EXEC ('SELECT * FROM Production.Product') – exécution de la chaîne constante tsql ———— DÉCLARER @Nom de la table sysname ="Sales.SalesOrderHeader" EXÉCUTER ('CHOISIR * DE'+@Nom de la table) – Chaîne T-SQL avec variable – dynamique ———— EXÉCUTER sp_executeSQL N'SELECT * FROM Purchasing.PurchaseOrderHeader '————DÉCLARER @SQL varchar(256); ENSEMBLE @SQL='SELECT * FROM Production.Product' EXEC (@SQL) – Exécution de requêtes SQL dynamiques SQL Server 2008———— DÉCLARER @SQL varchar(256), @Tableau sysname; ENSEMBLE @SQL='CHOISIR * DE'; ENSEMBLE @Tableau = 'Production.Produit' ENSEMBLE @SQL = @SQL+''+@Tableau IMPRIMER @SQL – pour le débogage du SQL dynamique avant l'exécution du code statique généré EXEC (@SQL) – Exécution SQL dynamique Microsoft – SQL Server 2005 exécute SQL dynamique ———— – Requêtes dynamiques avec variables locales et paramètre (s) – Requête paramétrée t-sql – Les paramètres ne sont PAS AUTORISÉS partout dans une instruction SQL – d'où SQL dynamique DÉCLARER @Couleur varchar(16) = 'Jaune' SÉLECTIONNER Couleur=@Couleur, ProductCount=COMPTER(Couleur) DE AdventureWorks2008.Production.Produit OÙ Couleur = @Couleur / * Couleur ProductCount Jaune 36 * /———— – Utilisation de sp_executeSQL avec paramètres d'entrée et de sortie – ******* – DYNAMIC SQL PARAMETERISE A LA SECURITE, LES PERFORMANCES ET D'AUTRES AVANTAGES – ******* – UTILISEZ TOUJOURS DYNAMIC SQL PARAMETERISÉ SI POSSIBLE – ******* DÉCLARER @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024) DÉCLARER @Liste des prix de l'argent = 2000,0, @LastProduct varchar(64) ENSEMBLE @SQL = N'SELECT @pLastProduct = max (Nom) DE AdventureWorks2008.Production.Product WHERE ListPrice> = @pListPrice ' ENSEMBLE @ParmDefinition = N '@ pListPrice argent, @pLastProduct varchar (64) SORTIE ' EXÉCUTER sp_executeSQL – T-SQL dynamique @SQL, @ParmDefinition, @pListPrice = @Liste des prix, @pLastProduct=@LastProduct PRODUCTION SÉLECTIONNER [ListPrice >=]=@Liste des prix, DernierProduit=@LastProduct / * ListPrice> = DernierProduit 2000.00 Touring-1000 Jaune, 60 * / – ******* Article: Exécuter des commandes SQL dynamiques dans SQL Server – ******* ————– Utilisation de SQL Server 2012 sp_executeSQL avec paramètres d'entrée et de sortie (2008/2005) – Exécution de la dénomination en 3 parties de SQL dynamique – Spécification de la base de données (USE dbname) EXEC AdventureWorks2008R2.dbo.sp_executeSQL N'exec sp_help ' ———— – INSERT EXEC exécution SQL dynamique CRÉER TABLEAU #Produit(ProductID int, Nom du produit varchar(64)) INSÉRER #Produit EXEC sp_executeSQL N'SELECT ProductID, Nom DE AdventureWorks2008.Production.Product ' – (504 ligne (s) affectée (s)) SÉLECTIONNER * DE #Produit ORDRE PAR Nom du produit VA TOMBER TABLEAU #Produit ———— ———— – Parcourez toutes les bases de données pour obtenir le nombre de tables – Didacticiel SQL dynamique – Changer dynamiquement le contexte de la base de données (utiliser la substitution de base de données) – Utilisez CONCAT à partir de SQL Server 2012 ———— DÉCLARER @SQL nvarchar(max), @dbName sysname; DÉCLARER DBcursor LE CURSEUR POUR SÉLECTIONNER Nom DE Maître.dbo.bases de données système OÙ Nom NE PAS DANS ('Maître',«tempdb»,'maquette',«msdb») ET DATABASEPROPERTYEX(Nom,'statut') ='EN LIGNE' ORDRE PAR Nom; OUVERT DBcursor; ALLER CHERCHER DBcursor DANS @dbName; TANDIS QUE (@@ FETCH_STATUS = 0) – boucle à travers tous les db-s COMMENCER DÉCLARER @dbContext nvarchar(256) =@dbName+«.dbo.»+'sp_executeSQL' ENSEMBLE @SQL = CONCAT ('SELECT' 'Base de données:', @dbName, 'table count' '= COUNT (*) FROM sys.tables'); IMPRIMER @SQL; – SELECT 'Database: AdventureWorks table count' = COUNT (*) FROM sys.tables EXEC @dbContext @SQL; ALLER CHERCHER DBcursor DANS @dbName; FINIR; — tandis que FERMER DBcursor; DÉALLOCER DBcursor; ———- ———— – SQL Server Assemble, Test & Execute Dynamic SQL Command – Didacticiel Dynamic SQL– Générer des instructions SQL dynamiques dans SQL Server 2008 ———— DÉCLARER @SQLStatement nvarchar(max) – Variable de chaîne pour l'assemblage de l'instruction SQL DÉCLARER @ColumnList nvarchar(max), @Où nvarchar(max), @Tableau nvarchar(max) ENSEMBLE @ColumnList = 'ProductID, ProductName = Name, ListPrice' ENSEMBLE @Tableau = 'AdventureWorks2008.Production.Product' ENSEMBLE @Où = "La couleur n'est pas nulle" ENSEMBLE @SQLStatement = 'CHOISIR' + @ColumnList + CARBONISER(13) + ' DE '+@Tableau + CARBONISER(13) + 'O 1 = 1 ET' + @Où IMPRIMER @SQLStatement / * SELECT ProductID, ProductName = Name, ListPrice DE AdventureWorks2008.Production.Product WHERE 1 = 1 AND La couleur n'est pas nulle * / EXÉCUTER sp_executeSQL @SQLStatement – Exécuter Dynamic SQL – T-SQL Dynamic SQL – (256 ligne (s) affectée (s)) ———— – Alias ​​dynamique pour colonne / table – SQL Server EXECUTE DÉCLARER @Légende varchar(32) ="ProductName" EXEC ('CHOISIR le nom AS'+@Légende+'DE AdventureWorks2008.Production.Product') ———— – SQL dynamique pour le nombre de lignes dans toutes les tables – utilisation des métadonnées de la base de données dans SQL dynamique DÉCLARER @SQL nvarchar(max), @Schéma sysname, @Tableau sysname; ENSEMBLE @SQL = '' SÉLECTIONNER @SQL = @SQL + 'CHOISIR' ''+QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(NOM DE LA TABLE) +'' ''+ '= COUNT (*) DE'+ QUOTENAME(TABLE_SCHEMA) +'.'+QUOTENAME(NOM DE LA TABLE) +';' DE INFORMATION_SCHEMA.LES TABLES OÙ TABLE_TYPE='TABLE BASE' IMPRIMER @SQL – test et débogage EXEC sp_executesql @SQL – Exécution de requêtes SQL dynamiques – sp_executesql SQL Server ———— – Code équivalent avec le sp_MSforeachtable non documenté EXEC sp_MSforeachtable 'sélectionner' '?' ', compter (*) à partir de?'———— ———— – Variables SQL dynamiques SQL Server – résultat en variable – entrée à partir d'une variable ———— DÉCLARER @Nom de famille nvarchar(32) = 'Forgeron', @MaxFirstName NVARCHAR(50) DÉCLARER @SQL NVARCHAR(MAX) = N'SELECT @pMaxFirstNameOUT = max (QUOTENAME (FirstName)) DE AdventureWorks2008.Person.Person '+CARBONISER(13) + 'WHERE LastName = @pLastName' IMPRIMER @SQL+CARBONISER(13) EXEC sp_executeSQL @SQL, – obtenir une entrée variable / définir une sortie variable N '@ pLastName nvarchar (32), @pMaxFirstNameOUT nvarchar (50) SORTIE ', – définition des paramètres @pLastName = @Nom de famille, – paramètre d'entrée @pMaxFirstNameOUT=@MaxFirstName PRODUCTION – paramètre de sortie SÉLECTIONNER [Max First Name] = @MaxFirstName, Légende='des noms de famille', Nom de famille=@Nom de famille / * Max Prénom Légende Nom [Zachary] des prénoms Smith * / ———– – Procédure stockée SQL Server dynamique SQL – Instruction SQL paramétrée ———– – Le SQL dynamique n'est pas autorisé dans la fonction (UDF)

CRÉER PROCÉDURE uspProductSearch @ProductName VARCHAR(32) = NUL COMME COMMENCER DÉCLARER @SQL NVARCHAR(MAX) SÉLECTIONNER @SQL = 'SELECT ProductID, ProductName = Nom, Couleur, ListPrice' + CARBONISER(dix) + 'DE AdventureWorks2008.Production.Product' + CARBONISER(dix) + «O 1 = 1» + CARBONISER(dix) SI @ProductName EST NE PAS NUL SÉLECTIONNER @SQL = @SQL + 'AND Name LIKE @pProductName' IMPRIMER @SQL – exécution paramétrée EXEC sp_executesql @SQL, N '@ pProductName varchar (32)', @ProductName FINIR VA – Exécuter une procédure stockée SQL dynamique avec paramètre EXEC uspProductSearch '%bicyclette%' / * ProductID ProductName Color ListPrice …. 710 Chaussettes VTT, L Blanc 9.50 709 Chaussettes VTT, M Blanc 9.50 …. * / – ******* Article: Construction de SQL dynamique dans une procédure stockée – ******* ———— – SQL dynamique pour l'exécution d'OPENQUERY dans la procédure stockée ———— UTILISER AdventureWorks2008; VA CRÉER PROC sprocGetBOM @ProductID int, @Date Date COMME COMMENCER DÉCLARER @SQL nvarchar(max) = 'CHOISIR * DANS ## BOM FROM OPENQUERY (localhost, '' EXÉCUTER [AdventureWorks].[dbo].[uspGetBillOfMaterials] '+ convertir(varchar,@ProductID) + ',' '' ''+convertir(varchar,@Date) +'' '' '' ')' IMPRIMER @SQL EXEC sp_executeSQL @SQL FINIR VA EXEC sprocGetBOM 900, «15/03/2004» VA SÉLECTIONNER * DE ## BOM – Table temporaire globale avec une portée en dehors du sproc – (24 ligne (s) concernée (s)) TOMBER TABLEAU ## BOM ———— – Tri dynamique avec classement spécifique – ORDER BY dynamique DÉCLARER @SQL nvarchar(max) ='SELECT ProductID, Name, ListPrice, Color DE AdventureWorks2008.Production.Product COMMANDER PAR Nom ' DÉCLARER @Collation nvarchar(max) = 'COLLATE SQL_Latin1_General_CP1250_CS_AS' ENSEMBLE @SQL=@SQL + @Collation IMPRIMER @SQL EXEC sp_executeSQL @SQL – Exécuter une requête dynamique SQL Server———— – Exécution de procédure stockée dynamique (ad hoc) avec paramètre (s) DÉCLARER @spName varchar(256) = '[AdventureWorks2008].[dbo].[uspGetManagerEmployees]' DÉCLARER @ valParm1 int = 1 EXÉCUTER @spName @ valParm1————

– Compter les tables dans toutes les bases de données sans parcourir les métadonnées de la base de données ENSEMBLE NOCOUNT AU; CRÉER TABLEAU #DBTables ( DBName SYSNAME, TableCount INT ); DÉCLARER @DynamicSQL NVARCHAR(MAX) = ''; SÉLECTIONNER @DynamicSQL = @DynamicSQL + 'UTILISER' + QUOTENAME(Nom) +'; ' + 'insérer dans #DBTables sélectionnez' + Nom du devis(Nom,'' '') + ', count (*) de sys.tables; ' +carboniser(13) DE sys.bases de données; IMPRIMER @DynamicSQL; EXEC sp_executeSQL @DynamicSQL; SÉLECTIONNER * DE #DBTables ORDRE PAR TableCount DESC; VA TOMBER TABLEAU #DBTables; ———— – Créer une base de données dynamiquement UTILISER AdventureWorks2008; VA CRÉER PROC uspCreateDB @DBName sysname COMME COMMENCER DÉCLARER @SQL nvarchar(255) = 'CRÉER UNE BASE DE DONNÉES'+@DBName; EXEC sp_executeSQL @SQL; FINIR VA EXEC uspCreateDB 'InventaireJUL'; ———— Article connexe: SQL dynamique – Procédure stockée SQL dynamique pour la liste des clients UTILISER Vent du nord; VA SI EXISTE (SÉLECTIONNER * DE sys.objets OÙ object_id = Object_id(N '[dbo].[CustomerListByState]') ET TAPER DANS (N'P ',N'PC ')) TOMBER PROCÉDURE [dbo].[CustomerListByState] VA – Procédure stockée SQL dynamique – Clause de liste Dynamic SQL IN / ***** AVERTISSEMENT – Ce sproc est vulnérable aux attaques par injection SQL ***** / – Le séparateur de liste CSV et JOIN est la solution préférée CRÉER PROCÉDURE CustomerListByState @États VARCHAR(128) COMME COMMENCER DÉCLARER @SQL NVARCHAR(1024) ENSEMBLE @SQL = 'sélectionnez CustomerID, CompanyName, ContactName, Phone, Région des clients où Région DANS (' + @États + ')' + 'ordre par région' IMPRIMER @SQL – Pour les tests et le débogage / * La requête suivante est exécutée en SQL dynamique sélectionnez CustomerID, CompanyName, ContactName, Phone, Region de clients où la région IN ('WA', 'OR', 'ID', 'CA') trié par région * / – Exécution SQL dynamique EXEC Sp_executesql @SQL FINIR VA – Exécuter une procédure stockée SQL dynamique DÉCLARER @États VARCHAR(100) ENSEMBLE @États = '' 'WA' ',' 'OU' ',' 'ID' ',' 'CA' '' EXEC CustomerListByState @États VA / * Résultats partiels CustomerID CompanyName ContactName Téléphone Région LETSS Let's Stop N Shop Jaime Yorres (415) 555-5938 CA SAVEA Save-a-lot Markets Jose Pavarotti (208) 555-8097 ID GREAL Great Lakes Food Market Howard Snyder (503) 555-7555 OU Magasin d'importation de coyote affamé HUNGC Yoshi Latimer (503) 555-6874 OU * /

/ * QUOTENAME peut également être utilisé pour construire une chaîne d'exécution de sproc DÉCLARER @States VARCHAR (100) SET @States = QUOTENAME ('WA', '' '') + ',' + QUOTENAME ('OR', '' '') EXEC CustomerListByState @States VA * / ————

– Rendre TABLESAMPLE dynamique

– SQL dynamique pour TABLESAMPLE – SQL dynamique T-SQL – Échantillon de table SQL DÉCLARER @Taille minuscule = 7 DÉCLARER @SQL nvarchar(512) = 'SELECT PurchaseOrderID, OrderDate = CAST (OrderDate AS DATE), VendorID FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader TABLESAMPLE ('+ JETER(@Taille COMME VARCHAR) +'PERCENT)' IMPRIMER @SQL – à des fins de test et de débogage / * SELECT PurchaseOrderID, OrderDate = CAST (OrderDate AS DATE), VendorID FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader TABLESEMPLE (7 POUR CENT) * / – SQL Server exécute SQL dynamique EXEC sp_executesql @SQL VA – (435 ligne (s) concernée (s)) / * Résultats partiels PurchaseOrderID OrderDate VendorID 349 23/06/2003 1672 350 23/06/2003 1600 351 23/06/2003 1522 352 23/06/2003 1570 353 23/06/2003 1516 * / ————

– Exécution SQL dynamique avec paramètres

– Script SQL dynamique – SQL Server 2008 – Utilisation des paramètres sp_executesql – Communication avec le processus enfant à l'aide des paramètres de ligne de commande UTILISER AdventureWorks2008; DÉCLARER @ParmDefinition NVARCHAR(1024) = N '@ FirstLetterOfLastName char (1), @LastFirstNameOUT nvarchar (50) SORTIE ' DÉCLARER @Première lettre CARBONISER(1) = «P», @LastFirstName NVARCHAR(50) DÉCLARER @SQL NVARCHAR(MAX) = N'SELECT @LastFirstNameOUT = max (Prénom) DE Person.Person '+CARBONISER(13) + 'WHERE left (LastName, 1) = @FirstLetterOfLastName' IMPRIMER @SQL+CARBONISER(13) – Pour les tests et le débogage / * SELECT @LastFirstNameOUT = max (Prénom) DE Personne.Personne WHERE left (LastName, 1) = @FirstLetterOfLastName * / IMPRIMER @ParmDefinition – Pour les tests et le débogage / * @FirstLetterOfLastName char (1), @LastFirstNameOUT nvarchar (50) SORTIE * / – Valeur de retour SQL Server dynamique SQL – Retour des valeurs à partir de SQL dynamiqueEXÉCUTER sp_executeSQL @SQL, @ParmDefinition, @FirstLetterOfLastName = @Première lettre, @LastFirstNameOUT=@LastFirstName PRODUCTION SÉLECTIONNER [Last First Name] = @LastFirstName, Légende='des noms commençant par', Lettre=@Première lettre VA ———— – Énumérer tous les objets dans les bases de données – Les objets de retour comptent dans toutes les bases de données sur le serveur – Procédure stockée SQL dynamique – Nom de citation SQL utilisé pour créer des noms d'objets de base de données valides UTILISER Aventure; VA SI EXISTE (SÉLECTIONNER * DE sys.objets OÙ object_id = Object_id(N '[dbo].[sprocObjectCountsAllDBs]') ET TAPER DANS (N'P ',N'PC ')) TOMBER PROCÉDURE [dbo].[sprocObjectCountsAllDBs] VA CRÉER PROC sprocObjectCountsAllDBs COMME COMMENCER DÉCLARER @dbName SYSNAME, @ObjectCount INT DÉCLARER @SQL NVARCHAR(MAX) DÉCLARER @DBObjectStats TABLEAU( DBName SYSNAME, DBObjects INT ) DÉCLARER curAllDBs LE CURSEUR POUR SÉLECTIONNER Nom DE MAÎTRE.dbo.bases de données système OÙ Nom NE PAS DANS ('Maître',«tempdb»,'maquette',«msdb») ORDRE PAR Nom OUVERT curAllDBs ALLER CHERCHER curAllDBs DANS @dbName TANDIS QUE (@@ FETCH_STATUS = 0) – boucle à travers tous les db-s COMMENCER – Construire une instruction SQL valide mais câblée – SQL QUOTENAME est utilisé pour la formation d'identifiant valide ENSEMBLE @SQL = 'select @dbObjects = count (*)' + Carboniser(13) + 'de ' + Nom du devis(@dbName) + «.dbo.sysobjects» IMPRIMER @SQL – Utilisez-le pour le débogage – Appel SQL dynamique avec paramètre (s) de sortie EXEC Sp_executesql @SQL , N '@ dbObjects int sortie' , @dbObjects = @ObjectCount PRODUCTION INSÉRER @DBObjectStats SÉLECTIONNER @dbName, @ObjectCount ALLER CHERCHER curAllDBs DANS @dbName FINIR — tandis que FERMER curAllDBs DÉALLOCER curAllDBs – Renvoyer les résultats SÉLECTIONNER * DE @DBObjectStats FINIR VA – Exécuter une procédure stockée avec SQL dynamique EXEC sprocObjectCountsAllDBs VA ———— – Fréquence de la valeur de la colonne dans la base de données ———— – Trouver la fréquence d'une valeur de colonne (800) dans toutes les tables avec la colonne ProductID ———— DÉCLARER @DynamicSQL nvarchar(MAX) = '', @ProductID int = «800»; DÉCLARER @Nom de colonne sysname = N'ProductID ', @Parms nvarchar(32) = N '@ pProductID int'; SÉLECTIONNER @DynamicSQL = @DynamicSQL + CAS LORSQUE LEN(@DynamicSQL) <> 0 ENSUITE carboniser(13) +'UNION TOUS' AUTRE '' FINIR + 'CHOISIR' ''+ t.TABLE_SCHEMA+'.'+ t.NOM DE LA TABLE + '' 'AS TableName, Frequency = COUNT (*) FROM' + QUOTENAME(t.TABLE_SCHEMA) +'.' + QUOTENAME(t.NOM DE LA TABLE) + 'O CONVERTIR (INT,' + QUOTENAME(c.NOM DE COLONNE) + ') = @pProductID' DE [AdventureWorks2008].[INFORMATION_SCHEMA].[TABLES] t INTÉRIEUR REJOINDRE [AdventureWorks2008].[INFORMATION_SCHEMA].[COLUMNS] c AU t.TABLE_SCHEMA = c.TABLE_SCHEMA ET t.NOM DE LA TABLE = c.NOM DE LA TABLE OÙ TABLE_TYPE='TABLE BASE' ET c.NOM DE COLONNE = @Nom de colonne; ENSEMBLE @DynamicSQL = @DynamicSQL + N 'ORDER BY Frequency DESC;'; IMPRIMER @DynamicSQL; EXEC sp_executeSQL @DynamicSQL, @Parms, @pProductID = @ProductID; / * TableName Fréquence Ventes.VentesCommandeDétail 495 Production.TransactionHistory 418 …. * /———— – Génération automatique de code pour – conversion datetime du style 0 à 14 – SQL dynamique utilisant une table temporaire – Table temporaire SQL Server Dynamic SQL UTILISER AdventureWorks2008; DÉCLARER @JE INT = –1 DÉCLARER @SQLDynamic nvarchar(1024) CRÉER TABLEAU #SQL(STYLE int, SQL varchar(256), Résultat varchar(32)) TANDIS QUE (@JE < 14) COMMENCER ENSEMBLE @JE + = 1 INSÉRER #SQL(STYLE, SQL) SÉLECTIONNER @JE, 'CHOISIR'+ 'CONVERTIR (VARCHAR, GETDATE (),'+CONVERTIR(VARCHAR,@JE) +')' ENSEMBLE @SQLDynamic = 'UPDATE #SQL SET Résultat = (SELECT CONVERTIR (VARCHAR, GETDATE (), '+CONVERTIR(VARCHAR,@JE) + ')) WHERE STYLE ='+ CONVERTIR(VARCHAR,@JE) IMPRIMER @SQLDynamic / * Imprimé dans les messages – liste partielle UPDATE #SQL SET Résultat = (SELECT CONVERTIR (VARCHAR, GETDATE (), 0)) O STYLE = 5 * / EXEC sp_executeSQL @SQLDynamic FINIR SÉLECTIONNER * DE #SQL TOMBER TABLEAU #SQL ———— – Communiquer entre le parent et – processus fils utilisant une table temporaire partagée – La table temporaire créée dans la session parent est visible par l'enfant – Session parents – SQL dynamique DÉCLARER @SQL nvarchar(512) SÉLECTIONNER Ville=«Montréal», Pays = convertir(varchar(30),'Canada') DANS #Ville – Script T-SQL de session enfant ENSEMBLE @SQL = 'SELECT * FROM #Ville; INSERT #City VALUES ('' Dallas '', '' United States ''); '

/ * char (39) (guillemet simple ascii) peut être utilisé pour éliminer les guillemets imbriqués INSÉRER LES VALEURS #Ville ('+ char (39) +' Dallas '+ char (39) +', '+ char (39) + 'États-Unis' + car (39) + ');' * / IMPRIMER @SQL / * SÉLECTIONNEZ * DE #Ville; INSÉRER LES VALEURS #City ('Dallas', 'États-Unis'); * / EXEC sp_executeSQL @SQL / * Résultat de la sélection dans la session enfant Ville Pays Montréal Canada * / SÉLECTIONNER * DE #Ville VA /* Résultats Ville Pays Dallas États-Unis Montréal Canada * / TOMBER TABLEAU #Ville VA ———— – L'importance de l'utilisation de QUOTENAME – QUOTENAME garantit que le SQL dynamique s'exécutera correctement UTILISER Vent du nord; VA – SQL crée une procédure stockée – SQL SQL dynamique – Utilisation du guillemet SQL CRÉER PROCÉDURE SelectFromAnyTable @TableName nvarchar(256) COMME COMMENCER DÉCLARER @SQL NVARCHAR(512) SI NE PAS EXISTE (SÉLECTIONNER 1 DE INFORMATION_SCHEMA.LES TABLES OÙ NOM DE LA TABLE = @Nom de la table) COMMENCER RAISERROR('Nom de table non valide% s!',16,1, @Nom de la table) REVENIR 0 FINIR ENSEMBLE @SQL = 'sélectionner * à partir de' + QUOTENAME(@Nom de la table) IMPRIMER @SQL – sélectionnez * parmi [Order Details] EXÉCUTER sp_executeSQL @SQL REVENIR 1 FINIR – Script d'exécution SQL – SQL exécute une procédure stockée SQL dynamique EXEC SelectFromAnyTable 'Détails de la commande' / * Résultats partiels OrderID ProductID UnitPrix Quantité Remise 10248 11 14,00 12 0 10248 42 9,80 10 0 10248 72 34,80 5 0 10249 14 18,60 9 0 * / ———— – Requête pivot dynamique pour le tableau croisé – Rapport de tableau croisé pivot SQL – Pivot dynamique SQL – SQL SQL dynamique – Rapport de tableau croisé dynamique SQL avec pivot UTILISER Aventure VA DÉCLARER @SQLtext COMME NVARCHAR(MAX) DÉCLARER @ReportColumnNames COMME NVARCHAR(MAX) – Génération dynamique de listes de pivot SQL – Liste pivot dynamique SÉLECTIONNER @ReportColumnNames = Truc( ( SÉLECTIONNER ',' + QUOTENAME(AAAA) COMME [text()] DE (SÉLECTIONNER DISTINCT AAAA=JETER (An(Date de commande) comme VARCHAR) DE Ventes.SalesOrderHeader ) X ORDRE PAR AAAA – Chemin SQL xml pour la génération de listes limitées par des virgules POUR XML CHEMIN ('')), 1, 1, '') ENSEMBLE @SQLtext = N'SELECT * FROM (SELECT [Store (Freight Summary)]= s.Nom, YEAR (OrderDate) AS OrderYear, Freight = convert (money, convert (varchar, Freight)) FROM Sales.SalesOrderHeader soh REJOIGNEZ Sales.Store s ON soh.CustomerID = s.CustomerID) comme en-tête PIVOT (SOMME (Fret) POUR OrderYear IN (' + @ReportColumnNames + N')) COMME Pvt ORDRE DE 1 ' IMPRIMER @SQLtext – Test et débogage / * SÉLECTIONNER * DE (SÉLECTIONNER [Store (Freight Summary)]= s.Nom, YEAR (OrderDate) AS OrderYear, Freight = convert (money, convert (varchar, Freight)) FROM Sales.SalesOrderHeader soh REJOIGNEZ Sales.Store s ON soh.CustomerID = s.CustomerID) comme en-tête PIVOT (SOMME (Fret) POUR OrderYear IN ([2001],[2002],[2003],[2004])) COMME Pvt ORDRE PAR 1 * / – Exécution de requêtes dynamiques SQL EXEC sp_executesql @SQLtext VA / * Résultats partiels Magasin (résumé du fret) 2001 2002 2003 2004 Articles de sport divers 1074,02 4609,31 4272,94 1569,04 Vélos Sunny Place 193,95 802,70 1095,83 411,62 Magasin Super Sports 102,15 743,51 427,80 301,68 Ventes et réparations exceptionnelles 1063,69 1547,73 37,28 13,23 * / ———— – Exécution d'instruction SQL dynamique simple – Didacticiel SQL dynamique EXEC sp_executeSQL N'SELECT TOP (7) * FROM Northwind.dbo.Orders ORDER BY NEWID () '———— Clause WHERE SQL dynamiqueDÉCLARER @Prédicat varchar(128) = 'ProductID = 800'EXEC ('SÉLECTIONNER * DANS AdventureWorks2008.Production.Product WHERE'+@Prédicat)———— Nom de la vue dynamique / nom de la table SELECTDÉCLARER @SQL nvarchar(max), @Vue sysname=«Northwind.dbo.Invoices»SÉLECTIONNER @SQL = 'CHOISIR * DE' + @VueEXEC sp_executeSQL @SQL ———— – Dynamic SQL – nom de table dynamique – SQL Server SQL dynamique – sp_executeSQL DÉCLARER @SQL nvarchar(max), @Tableau sysname='AdventureWorks2008.Production.Product'SÉLECTIONNER @SQL = 'SELECT Rows = count (*) FROM' – compter les lignes instruction SQL dynamique SÉLECTIONNER @SQL = @SQL + @Tableau – concaténer des variables de chaîne EXEC (@SQL) – Commande d'exécution SQL dynamique d'origine– 504 – SQL exécute un résultat SQL dynamiqueEXEC sp_executeSQL @SQL – Exécution SQL dynamique transact-SQL améliorée– 504———— Le script SQL dynamique génère des instructions T-SQL statiques (câblées) au moment de l'exécution. Nous pouvons utiliser la commande PRINT pour voir le fiscript SQL final et testez-le avant l'exécution. – Procédure stockée de requête SQL dynamique SQL Server 2008 – Syntaxe rapide – T-SQL dynamiqueUTILISER AdventureWorks2008; VA CRÉER PROCÉDURE uspCountAnyKeyInAnyTable @Nom de la table SYSNAME, @Nom de colonne SYSNAME, @Wildcard NVARCHAR(64) COMME DÉCLARER @SQL NVARCHAR(MAX)='SELECT FrequencyCount = count (*)' + ' DE ' + @Nom de la table + ' OÙ ' + @Nom de colonne + ' AIMER ' + CARBONISER(39) +@Wildcard + CARBONISER(39) IMPRIMER @SQL – pour les tests et le débogage EXEC sp_executesql @SQL VA EXÉCUTER uspCountAnyKeyInAnyTable 'Production.Produit', 'Couleur', '%Bleu%' – 26———— Dynamic SQL est une puissante technologie de programmation de base de données qui vous permet de concaténer et d'exécuter des instructions T-SQL de manière dynamique dans une variable chaîne au moment de l'exécution. Vous pouvez créer des requêtes robustes basées sur des données / paramètres et des procédures stockées. Par exemple, la liste des colonnes du tableau croisé PIVOT change au fur et à mesure que les données augmentent, elle doit donc être créée de manière dynamique au moment de l'exécution et ne peut pas être câblée. Lors de la reconstruction d'index dans une boucle de curseur WHILE, le nom de la table varie, cela nécessite l'utilisation de SQL dynamique.Le SQL statique reste le même à chaque exécution. Les chaînes SQL dynamiques contiennent le texte d'un script T-SQL DML ou DDL et peuvent également contenir des espaces réservés pour les paramètres de liaison. Dans l'exemple suivant, @pCountryCode est un espace réservé pour un paramètre qui est fourni au moment de l'exécution. ————– Exemple pour déclarer et passer le paramètre (@pCountryCode) à sp_executesql————CRÉER PROC sprocListStatesByCountry @CountryCode varchar(32) COMMEDÉCLARER @SQL nvarchar(max)DÉCLARER @Colonnes varchar(128) = 'StateProvinceCode, CountryRegionCode, State = Nom'ENSEMBLE @SQL = 'CHOISIR' + @Colonnes + 'DE AdventureWorks2008.Person.StateProvince' + 'WHERE CountryRegionCode = @pCountryCode'EXEC sp_executesql @SQL, N '@ pCountryCode nvarchar (32)',@pCountryCode = @Code postalVAEXEC sprocListStatesByCountry «DE»– (résultats partiels) PAR DE Bayern ———— La fonction QUOTENAME renvoie une chaîne Unicode avec les crochets ajoutés pour faire de la chaîne d'entrée un identificateur délimité Microsoft SQL Server valide. SELECT QUOTENAME ('Détails de la commande') renvoie [Order Details], un identifiant valide avec espace. Si un l'identifiant est obtenu à partir des métadonnées, l'utilisation de QUOTENAME garantit la validitéité. – Exemple de requête dynamique T-SQL avec QUOTENAME – compter les lignes dans toutes les tables de la base de donnéesUTILISER AdventureWorks2008; DÉCLARER @SQLtext nvarchar(max) = '', @Schéma sysname, @Tableau sysname; SÉLECTIONNER @SQLtext = @SQLtext + 'CHOISIR' ''+QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(NOM DE LA TABLE) +'' ''+ '= COUNT (*) DE'+ QUOTENAME(TABLE_SCHEMA) +'.'+QUOTENAME(NOM DE LA TABLE) +';' DE INFORMATION_SCHEMA.LES TABLES OÙ TABLE_TYPE='TABLE BASE' ORDRE PAR TABLE_SCHEMA, NOM DE LA TABLE IMPRIMER @SQLtext – test et débogage / * ….; SELECT '[Production].[ProductDescription]'= COUNT (*) DE [Production].[ProductDescription]; …. * / EXEC sp_executesql @SQLtext – sql server exec dynamic sql – Code équivalent avec le sp_MSforeachtable non documenté – requête dynamique EXEC sp_MSforeachtable 'sélectionnez' '?' ', comptez (*) dans AdventureWorks2008.?' – Sp_MSforeachdb non documenté associé EXEC sp_MSforeachdb 'sélectionner' '?' '' L'opérateur T-SQL PIVOT fait pivoter les lignes en colonnes avec l'application une fonction d'agrégation telle que SUM (tableau croisé). Cependant, l'instruction PIVOT nécessite une liste de colonnes statique (câblée) qui on d'autre part peut-être basé sur les données, pas connu à l'avance, pas connu pour l'avenir, comme les années pour les colonnes qui changent avec le contenu des données d'une base de données. La solution est un SQL PIVOT dynamique avec préparation et utilisation de listes de colonnes dynamiques dans le PIVOT.

———— – Requête PIVOT dynamique SQL Server – Tableau croisé dynamique de pivot T-SQL – Colonnes dynamiques ———— – Nombre de colonnes inconnu – Exemple SQL dynamique – Requête dynamique t sql UTILISER Aventure; DÉCLARER @SQLtext COMME NVARCHAR(MAX) DÉCLARER @ReportColumnNames COMME NVARCHAR(MAX) – Génération dynamique de liste de pivot SQL – Liste de pivot dynamique – pivot dynamique SÉLECTIONNER @ReportColumnNames = Truc( ( SÉLECTIONNER ',' + QUOTENAME(AAAA) COMME [text()] DE (SÉLECTIONNER DISTINCT AAAA=JETER (An(Date de commande) comme VARCHAR) DE Ventes.SalesOrderHeader ) X ORDRE PAR AAAA POUR XML CHEMIN ('')), 1, 1, '') – Chemin SQL xml pour la génération de listes limitées par des virgules IMPRIMER @ReportColumnNames – [2001], [2002], [2003], [2004] ENSEMBLE @SQLtext = N'SELECT * FROM (SELECT [Store (Freight Summary)]= s.Nom, YEAR (OrderDate) AS OrderYear, Fret = convertir (argent, convertir (varchar, fret)) FROM Sales.SalesOrderHeader soh INNER JOIN Sales.Store s ON soh.CustomerID = s.CustomerID) comme en-tête PIVOT (SOMME (Fret) POUR OrderYear DANS(' + @ReportColumnNames + N ')) COMME Pvt COMMANDER PAR 1 ' IMPRIMER @SQLtext – Test et débogage – affiche la requête avant l'exécution – SQL Server t sql exécute SQL dynamique EXEC sp_executesql @SQLtext – Exécuter une commande SQL dynamique VA / * Résultats partiels Magasin (résumé du fret) 2001 2002 2003 2004 Magasin de quartier NULL 2289.75 1120.64 NULL Vélos neufs et d'occasion 1242,99 4594,51 4390,48 1671,98 * / – SQL dynamique d'injection SQL – se protéger des attaques par injection SQL Article de sécurité important lié au SQL dynamique: Guide pratique pour se protéger de l'injection SQL dans ASP.NETHow To: Protect From SQL Injection in ASP.NET – SQL dynamique avec paramètres d'entrée / sortie: La procédure stockée système sp_executeSQL prend en charge l'utilisation des paramètres d'entrée et de sortie. Avec paramètres, exécutable SQL dynamiquee déclaration ressemble à une exécution de procédure stockée avec des paramètres. La lisibilité, la fonctionnalité et les performances (plan de requête mis en cache) sont améliorées avec l'application de paramètres.

– Exécution SQL dynamique avec paramètres d'entrée / sortie – Requête dynamique SQL Server – QUOTENAME – Variables SQL dynamiques mssql UTILISER AdventureWorks2008; DÉCLARER @ParmDefinition NVARCHAR(1024) = N '@ pFirstLetterOfLastName char (1), @pLastFirstNameOUT nvarchar (50) SORTIE ' DÉCLARER @Première lettre CARBONISER(1) = «E», @LastFirstName NVARCHAR(50) DÉCLARER @SQL NVARCHAR(MAX) = N'SELECT @pLastFirstNameOUT = max (QUOTENAME (FirstName)) DE Person.Person '+CARBONISER(13) + 'WHERE left (LastName, 1) = @pFirstLetterOfLastName' IMPRIMER @SQL+CARBONISER(13) / * SELECT @pLastFirstNameOUT = max (QUOTENAME (Prénom)) DE Personne.Personne WHERE left (LastName, 1) = @pFirstLetterOfLastName * / IMPRIMER @ParmDefinition / * @pFirstLetterOfLastName char (1), @pLastFirstNameOUT nvarchar (50) SORTIE * / – SQL dynamique avec paramètres, y compris le paramètre OUTPUT EXÉCUTER sp_executeSQL @SQL, @ParmDefinition, @pFirstLetterOfLastName = @Première lettre, @pLastFirstNameOUT=@LastFirstName PRODUCTION SÉLECTIONNER [Last First Name] = @LastFirstName, Légende='des noms commençant par', Lettre=@Première lettre VA /* Résultats Lettre de légende du nom de famille [Xavier] des noms commençant par E * / – Exécution SQL dynamique d'OPENQUERY: La lisibilité du code SQL dynamique est un défi. La raison en est que le code T-SQL de construction (concaténations) se mêle au code dynamique. Le formatage des deux codes est utile. L'utilisation de CHAR (39) pour les guillemets simples réduit la séquence de guillemets simples comme '' '' ''.

– OPENQUERY Exécution SQL dynamique – Code T-SQL SQL Server 2008DÉCLARER @SQLtext nvarchar(max) = 'CHOISIR * DE OPENQUERY (' + QUOTENAME(CONVERTIR(sysname, @@NOM DU SERVEUR)) + ', ''EXÉCUTER [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 400, '' '' 2003-11-21 '' '' '') ' IMPRIMER @SQLtext / * CHOISIR * À PARTIR D'OPENQUERY ([YOURSERVERSQL2008], 'EXÉCUTER [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3, '' 2003-12-01 '' ') * / EXEC sp_executeSQL @SQLtext– (64 ligne (s) concernée (s))————

Un nom de table ou un nom de colonne peut inclure un espace. If we do not enclose such a db object name in square brackets, the generated dynamic SQL string will be invalid. We can explicitly concatenate '[‘ and ‘]' or use the special function QUOTENAME. — Forming proper database object names : — T-SQL QUOTENAME function will add square bracket delimiters to system names USE Adventureworks; SELECT DatabaseObject = QUOTENAME(table_schema) + '.' + QUOTENAME(table_name), t.* FROM INFORMATION_SCHEMA.TABLES t WHERE table_type DANS ('VIEW','BASE TABLE') ET Objectproperty(Object_id(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)), 'IsMSShipped') = 0 GO /* Partial results DatabaseObject [Production].[ProductProductPhoto] [Sales].[StoreContact] [Person].[Address] [Production].[ProductReview] [Production].[TransactionHistory] */ — Forming proper database object names within a — dynamic SQL query stored procedure with QUOTENAME: — QUOTENAME will make dynamic SQL execute correctly USE Northwind; GO — SQL create stored procedure – SQL dynamic SQL – SQL QUOTENAME usage CREATE PROCEDURE SelectFromAnyTable @table sysname AS BEGIN DECLARE @sql nvarchar(512) SET @sql = 'select * from '+ QUOTENAME(@table) IMPRIMER @sql EXECUTE sp_executeSQL @sql END GO — SQL execute script – SQL execute dynamic sql stored procedure exec SelectFromAnyTable 'Order Details' /* Messages select * from [Order Details] (2155 row(s) affected) */ — Forming Year column header in dynamic crosstab query:

— SQL pivot crosstab report – SQL QUOTENAME – SQL dynamic pivot — SQL dynamic crosstab report with pivot – SQL dynamic sql USE AdventureWorks GO DECLARE @OrderYear AS TABLE( YYYY INT NOT NULL PRIMARY CLÉ ) DECLARE @SQLtext AS NVARCHAR(4000) INSERT INTO @OrderYear SELECT DISTINCT An(OrderDate) FROM Sales.SalesOrderHeader DECLARE @ReportColumnNames AS NVARCHAR(MAX), @IterationYear AS INT SET @IterationYear = (SELECT Min(YYYY) FROM @OrderYear) SET @ReportColumnNames = N'' — Assemble pivot list dynamically WHILE (@IterationYear IS NOT NULL) BEGIN SET @ReportColumnNames = @ReportColumnNames + N', ' + QUOTENAME(Cast(@IterationYear AS NVARCHAR(dix))) SET @IterationYear = (SELECT Min(YYYY) FROM @OrderYear WHERE YYYY > @IterationYear) END SET @ReportColumnNames = Substring(@ReportColumnNames,2,Len(@ReportColumnNames)) SET @SQLtext = N'SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, YEAR(OrderDate) AS OrderYear,  Freight = convert(money,convert(varchar, Freight)) FROM Sales.SalesOrderHeader soh  JOIN Sales.Store s ON soh.CustomerID = s.CustomerID) as Header PIVOT (SUM(Freight) FOR OrderYear IN(' + @ReportColumnNames + N')) AS Pvt ORDER BY 1' IMPRIMER @SQLtext — Testing & debugging — SQL QUOTENAME placed the square brackets around the year (YYYY) /* SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, YEAR(OrderDate) AS OrderYear,  Freight = convert(money,convert(varchar, Freight)) FROM Sales.SalesOrderHeader soh  JOIN Sales.Store s ON soh.CustomerID = s.CustomerID) as Header PIVOT (SUM(Freight) FOR OrderYear IN([2001],[2002],[2003],[2004])) AS Pvt ORDER BY 1 */

— Execute dynamic sql query EXEC Sp_executesql @SQLtext GO /* Partial results Store (Freight Summary)             2001        2002        2003        2004 Grease and Oil Products Company     104.02      555.02      726.75     272.28 Great Bicycle Supply                4430.26     3871.35     NULL        NULL Great Bikes                         1653.89     7445.16     7525.98    584.63 Greater Bike Store                  489.79      1454.78     864.08     245.22 */ — Forming database name in dynamic SQL:

— Return objects count in all databases on the server – SQL Server dynamic SQL — SQL Server QUOTENAME – SQL stored procedure – SQL dynamic query USE AdventureWorks; GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sprocAllDBsSysobjectCounts]') ET TYPE DANS (N'P',N'PC')) DROP PROCEDURE [dbo].[sprocAllDBsSysobjectCounts] GO CREATE PROC sprocAllDBsSysobjectCounts AS BEGIN SET NOCOUNT ON DECLARE @dbName SYSNAME, @ObjectCount INT DECLARE @SQLtext NVARCHAR(MAX) — SQL Server table variable DECLARE @DBObjectStats TABLE( DBName SYSNAME, DBObjects INT ) — SQL Server cursor DECLARE curAllDBs CURSOR FOR SELECT Nom FROM MASTER.dbo.sysdatabases — SQL NOT IN set operator – exclude system db-s WHERE Nom NOT DANS ('master','tempdb','model','msdb') ORDER BY Nom OPEN curAllDBs FETCH curAllDBs INTO @dbName WHILE (@@FETCH_STATUS = 0) — loop through all db-s BEGIN — Build valid yet hard-wired SQL statement SET @SQLtext = 'select @dbObjects = count(*)' + char(13) + 'from ' + — SQL QUOTENAME QUOTENAME(@dbName) + '.dbo.sysobjects' IMPRIMER @SQLtext — Use it for debugging /* Partial listing select @dbObjects = count(*) de [AdventureWorks].dbo.sysobjects */ — Dynamic sql call with output parameter(s) EXEC sp_executesql @SQLtext, N'@dbObjects int output' , @dbObjects = @ObjectCount OUTPUT INSERT @DBObjectStats SELECT @dbName, @ObjectCount FETCH curAllDBs INTO @dbName END — while CLOSE curAllDBs DEALLOCATE curAllDBs — Return results SELECT * FROM @DBObjectStats ORDER BY DBName END — sproc GO — Test stored procedure – sproc — Execute stored procedure statement EXEC sprocAllDBsSysobjectCounts GO /* Partial results DBName            DBObjects AdventureWorks    748 AdventureWorks3NF 749 AdventureWorksDW  169 AdvWorksDWX       137 Audit             48 */ — Forming schema & table id in dynamic SQL:

———— — SQL Server dbreindex fragmented indexes – using cursor, QUOTENAME & dynamic SQL — SQL Server BUILD indexes — Reindex all indexes over 35% logical fragmentation with 90% fillfactor ———— USE AdventureWorks2008; GO — Create temporary table to hold meta data information about indexes CREATE TABLE #IndexFragmentation ( ObjectName CHAR(255), ObjectId INT, IndexName CHAR(255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) INSERT #IndexFragmentation EXEC( 'DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES') GO DELETE #IndexFragmentation WHERE left(ObjectName,3) = 'sys' GO ALTER TABLE #IndexFragmentation ADD SchemaName SYSNAME NULL GO UPDATE [if] SET SchemaName = SCHEMA_NAME(schema_id) FROM #IndexFragmentation [if] INNER JOIN sys.objets o ON [if].ObjectName = o.Nom WHERE o.TYPE = 'U' — select * from #IndexFragmentation — SQL cursor — SQL dynamic SQL — SQL while loop DECLARE @MaxFragmentation DECIMAL = 35.0 DECLARE @Schema SYSNAME, @Table SYSNAME, @SQLtext NVARCHAR(512) DECLARE @objectid INT, @indexid INT DECLARE @Fragmentation DECIMAL — T-SQL cursor declaration DECLARE curIndexFrag CURSOR FOR SELECT SchemaName, ObjectName, LogicalFrag = max(LogicalFrag) FROM #IndexFragmentation WHERE LogicalFrag >= @MaxFragmentation ET indexid ! = 0 ET indexid ! = 255 GROUP BY SchemaName, ObjectName OPEN curIndexFrag FETCH NEXT FROM curIndexFrag INTO @Schema, @Table, @Fragmentation WHILE @@FETCH_STATUS = 0 BEGIN — T-SQL QUOTENAME SELECT @SQLtext = 'DBCC DBREINDEX (''' + QUOTENAME(RTRIM(@Schema)) + '.' + QUOTENAME(RTRIM(@Table)) + ''', '''', 90)' IMPRIMER @SQLtext — debug & test — Dynamic sql execution EXEC( @SQLtext) — Alternate (new way): EXEC sp_executeSQL @SQLtext FETCH NEXT FROM curIndexFrag INTO @Schema, @Table, @Fragmentation END CLOSE curIndexFrag DEALLOCATE curIndexFrag GO /* Partial messages DBCC DBREINDEX ('[Person].[StateProvince]', '', 90) DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC DBREINDEX ('[Sales].[Store]', '', 90) DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC DBREINDEX ('[Purchasing].[Vendor]', '', 90) DBCC execution completed. If DBCC printed error messages, contact your system administrator. */ — Cleanup DROP TABLE #IndexFragmentation GO ———— — Forming schema & sproc id in dynamic SQL:

———— — SQL Server T-SQL script generator dynamic SQL stored procedure ———— — SQL sproc dynamic parameter – when omitted, ALL is selected — SQL QUOTENAME SET nocount ON USE AdventureWorks; GO CREATE PROCEDURE ListAllSprocsInfo @SchemaPattern SYSNAME = NULL — set results to TEXT mode for execution AS BEGIN SET nocount ON DECLARE @SQLtext NVARCHAR(4000) SET @SQLtext='SELECT ''EXEC sp_help '' +''''''''+QUOTENAME(ROUTINE_SCHEMA) + ''.''  + QUOTENAME(ROUTINE_NAME) +'''''''' + CHAR(13) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE =''PROCEDURE''' IF @SchemaPattern IS NOT NULL SET @SQLtext = @SQLtext + N' AND ROUTINE_SCHEMA LIKE ''' + @SchemaPattern + '''' — print @SQLtext — test & debug EXEC sp_executeSQL @SQLtext — EXECUTE( @SQLtext) — old way END GO — Execute stored procedure — Set Query Results to Text in Management Studio Query Editor — Copy and paste results to new query window for execution EXEC ListAllSprocsInfo GO EXEC ListAllSprocsInfo 'Production' GO /* Partial results EXEC sp_help '[dbo].[uspPrintError]' EXEC sp_help '[dbo].[sprocPingLinkedServer]' EXEC sp_help '[dbo].[uspLogError]' */ ———— — Forming database name in dynamic SQL sproc:

———— — MSSQL assign table count in database to variable – QUOTENAME function ———— — Microsoft T-SQL dynamic sql stored procedure with input / output parameters ALTER PROCEDURE uspViewCount @DatabaseName SYSNAME, @Tables INT OUTPUT AS BEGIN DECLARE @SQLtext NVARCHAR(256), @Count INT SET @SQLtext = N'SELECT @Count = COUNT(*) FROM ' + QUOTENAME(@DatabaseName) + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''VIEW''' IMPRIMER @SQLtext — Debug & test /* SELECT @Count = COUNT(*) FROM [AdventureWorks2008].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW' */ — Dynamic SQL execution with output parameters EXEC sp_executesql @Query = @SQLtext , @Params = N'@Count INT OUTPUT' , @Count = @Count OUTPUT SET @Tables = @Count END GO — Microsoft SQL Server T-SQL execute stored procedure — SQL Assign sproc result to variable DECLARE @AWtables INT EXEC uspViewCount 'AdventureWorks2008' , @AWtables OUTPUT SELECT 'AdventureWorks2008 view count' = @AWtables GO /* Results AdventureWorks2008 view count 20 */ ———— — Forming servername in dynamic SQL sproc:

———— — Find where ProductID=3 is being used by select into from sproc execution ———— — SQL select into table create from sproc — T-SQL dynamic SQL OPENQUERY /* DATA ACCESS to current instance can be setup the following way exec sp_serveroption @server = 'PRODSVRSQL2008' ,@optname = 'DATA ACCESS' ,@optvalue = 'TRUE'

This way, OPENQUERY can be used against current instance (Usually OPENQUERY is used to access linked server.) */ DECLARE @sqlQuery nvarchar(max) = 'SELECT * INTO   BikePartsInAssembly FROM   OPENQUERY(' + QUOTENAME(CONVERT(sysname, @@SERVERNAME))+ ', ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3, ''''2003-12-01'''''')' IMPRIMER @sqlQuery /* SELECT * INTO   BikePartsInAssembly FROM   OPENQUERY([PRODSVRSQL2008], 'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3, ''2003-12-01''') */ EXEC sp_executeSQL @sqlQuery SELECT TOP ( 5 ) * FROM BikePartsInAssembly ORDER BY NEWID() GO /* Partial results ProductAssemblyID ComponentID       ComponentDesc 966               996               Touring-1000 Blue, 46 762               994               Road-650 Red, 44 956               996               Touring-1000 Yellow, 54 994               3                 LL Bottom Bracket 983               994               Mountain-400-W Silver, 46 */ — Cleanup DROP TABLE BikePartsInAssembly GO ———— — Changing database context in dynamic SQL:

———— — Dynamic SQL change database context — Executing dynamic SQL in a different database – USE dbname workaround ———— USE AdventureWorks2008; DECLARE @SQL nvarchar(max) = 'SELECT Tables=count(*) FROM sys.tables' DECLARE @dbName sysname = 'AdventureWorks' DECLARE @dbContext nvarchar(256)=@dbName+'.dbo.'+'sp_executeSQL' EXEC @dbContext @SQL — 72 ———— ———— — Nested dynamic SQL – create database object in another database ———— USE AdventureWorks2008; GO CREATE PROCEDURE sprocAlpha  @DatabaseName SYSNAME AS BEGIN SET NOCOUNT ON IF NOT EXISTS (SELECT 1 FROM sys.databases — SQL Injection attack prevention WHERE Nom=@DatabaseName) — Filter & user input RETURN (0) DECLARE @SQL NVARCHAR(MAX), @nestedSQL NVARCHAR(MAX) SET @nestedSQL = 'CREATE FUNCTION fnAlpha (@text varchar(16)) RETURNS varchar(64)     AS BEGIN DECLARE @output varchar(64) SET @output = @text+''/''+@text RETURN (@output) END' SET @nestedSQL = REPLACE(@nestedSQL,char(39),char(39) + CHAR(39)) IMPRIMER @nestedSQL SET @SQL = 'EXEC ' + @DatabaseName + '..sp_executeSQL N''' + @nestedSQL + '''' IMPRIMER @SQL EXEC sp_executeSQL @SQL RETURN (1) END GO EXEC sprocAlpha 'pubs' GO SELECT pubs.dbo.fnAlpha('London') — London/London

———— — Nested dynamic SQL – change database context – USE usage ———— USE AdventureWorks2008; DECLARE @SQL nvarchar(max)='use Northwind; DECLARE @nestedSQL nvarchar(max)= ''create trigger trgEmpInsert on Employees for insert as commencer select LinesInserted = count(*) from inserted finir ''; PRINT @nestedSQL EXEC sp_executesql @nestedSQL;' IMPRIMER @SQL — test & debug EXEC sp_executesql @SQL GO — Cleanup USE Northwind; DROP TRIGGER trgEmpInsert; ————

— Default & parameterized use of QUOTENAME: /* Typically square brackets (in dynamic SQL queries), single quotes and double quotes are used with QUOTENAME. */

— SQL QUOTENAME default usage – SQL brackets – SQL square brackets sélectionner QUOTENAME('Factory Order Detail') — Result: [Factory Order Detail] — SQL QUOTENAME equivalent use – MSSQL server quote name – bracketing name sélectionner QUOTENAME('Factory Order Detail','[]') — Result: [Factory Order Detail] — The second argument is: single quote, double quote, single quote sélectionner QUOTENAME('Factory Order Detail','"') — Result: "Factory Order Detail" sélectionner QUOTENAME('Factory Order Detail','()') — Result: (Factory Order Detail) — Search wildcard preparation to be used in dynamic SQL text search DECLARE @SearchKeyword nvarchar(32) = 'O''Reilly' DECLARE @SearchWildcard nvarchar(32) = QUOTENAME('%' + @SearchKeyword + '%',CHAR(39)+CHAR(39)) IMPRIMER @SearchKeyword IMPRIMER @SearchWildcard /* O'Reilly '%O''Reilly%' */ ———— — Related articles: Dynamic SQL & Stored Procedure Usage in T-SQL The Curse and Blessings of Dynamic SQL How to search using all or partial columns with Dynamic SQL while avoiding SQL Injection

SQL Server DBA Checklist — Dynamic SQL performance articles: Using sp_executesql Catch-all queries

Click to rate this post! [Total: 0 Average: 0]