Serveur d'impression

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

Par Titanfall , le 29 mai 2021 - 33 minutes de lecture

[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

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

– 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;

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

EXEC sprocGetBOM 900, «15/03/2004»

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;

TOMBER TABLEAU #DBTables;

————

– Créer une base de données dynamiquement

UTILISER AdventureWorks2008;

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

EXEC uspCreateDB 'InventaireJUL';

————

Article connexe: SQL dynamique

– Procédure stockée SQL dynamique pour la liste des clients

UTILISER Vent du nord;

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]

– 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

– Exécuter une procédure stockée SQL dynamique

DÉCLARER @États VARCHAR(100)

ENSEMBLE @États = '' 'WA' ',' 'OU' ',' 'ID' ',' 'CA' ''

EXEC CustomerListByState @États

/ * 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

* /

————

– 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 dynamique
EXÉ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

————

– É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;

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]

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

– Exécuter une procédure stockée avec SQL dynamique

EXEC sprocObjectCountsAllDBs

————

– 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

/* Résultats

Ville Pays

Dallas États-Unis

Montréal Canada

* /

TOMBER TABLEAU #Ville

————

– L'importance de l'utilisation de QUOTENAME

– QUOTENAME garantit que le SQL dynamique s'exécutera correctement

UTILISER Vent du nord;

– 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

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

/ * 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 dynamique
DÉ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 SELECT
DÉCLARER @SQL nvarchar(max), @Vue sysname=«Northwind.dbo.Invoices»
SÉLECTIONNER @SQL = 'CHOISIR * DE' + @Vue
EXEC 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 dynamique
EXEC 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 dynamique
UTILISER 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) COMME
DÉ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 postal
VA
EXEC 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ées
UTILISER 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

/ * 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

/* 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 2008
DÉ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

/* 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;

— SQL create stored procedure – SQL dynamic SQL – SQL QUOTENAME usage

CREATE PROCEDURE SelectFromAnyTable @table sysname

BEGIN

DECLARE @sql nvarchar(512)

SET @sql = 'select * from '+ QUOTENAME(@table)

IMPRIMER @sql

EXECUTE sp_executeSQL @sql

END

— 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

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

/* 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;

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]

CREATE PROC sprocAllDBsSysobjectCounts

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

— Test stored procedure – sproc

— Execute stored procedure statement

EXEC sprocAllDBsSysobjectCounts

/* 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;

— 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')

DELETE #IndexFragmentation

WHERE left(ObjectName,3) = 'sys'

ALTER TABLE #IndexFragmentation

ADD SchemaName SYSNAME NULL

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

/* 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

————

— 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;

CREATE PROCEDURE ListAllSprocsInfo

@SchemaPattern SYSNAME = NULL

— set results to TEXT mode for execution

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

— 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

EXEC ListAllSprocsInfo 'Production'

/* 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

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

— 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

/* Results

AdventureWorks2008 view count

————

— 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()

/* 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

————

— 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;

CREATE PROCEDURE sprocAlpha @DatabaseName SYSNAME

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

EXEC sprocAlpha 'pubs'

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

commencer

select LinesInserted = count(*) from inserted

finir

'';

PRINT @nestedSQL

EXEC sp_executesql @nestedSQL;'

IMPRIMER @SQL — test & debug

EXEC sp_executesql @SQL

— 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]

Articles relatifs:

Titanfall

Voir les publications de l'auteur

Commentaires

Laisser un commentaire

Cookie	Durée	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

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

Articles relatifs:

Tutos Gameserver respecte votre vie privée et vos données personnelles