sp_executesql (Transact-SQL)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft FabricWarehouse in Microsoft Fabric

Hiermee wordt een Transact-SQL-instructie of batch uitgevoerd die vaak opnieuw kan worden gebruikt of een batch die dynamisch is gebouwd. De Transact-SQL-instructie of batch kan ingesloten parameters bevatten.

Voorzichtigheid

Runtime-gecompileerde Transact-SQL-instructies kunnen toepassingen blootstellen aan schadelijke aanvallen. U moet uw query's parameteriseren wanneer u sp_executesqlgebruikt. Zie SQL-injectievoor meer informatie.

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis voor SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics en Analytics Platform System (PDW).

sp_executesql [ @stmt = ] N'statement'
[
    [ , [ @params = ] N'@parameter_name data_type [ { OUT | OUTPUT } ] [ , ...n ]' ]
    [ , [ @param1 = ] 'value1' [ , ...n ] ]
]

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.

Argumenten

Belangrijk

Argumenten voor uitgebreide opgeslagen procedures moeten worden ingevoerd in de specifieke volgorde, zoals beschreven in de sectie Syntaxis. Als de parameters niet in de volgorde zijn ingevoerd, treedt er een foutbericht op.

[ @stmt = ] N'instructie'

Een Unicode-tekenreeks die een Transact-SQL instructie of batch bevat. @stmt moet een Unicode-constante of een Unicode-variabele zijn. Complexere Unicode-expressies, zoals het samenvoegen van twee tekenreeksen met de operator +, zijn niet toegestaan. Tekenconstanten zijn niet toegestaan. Unicode-constanten moeten worden voorafgegaan door een N. De Unicode-constante N'sp_who' is bijvoorbeeld geldig, maar de tekenconstante 'sp_who' niet. De grootte van de tekenreeks wordt alleen beperkt door het beschikbare geheugen van de databaseserver. Op 64-bits servers is de grootte van de tekenreeks beperkt tot 2 GB, de maximale grootte van nvarchar(max).

@stmt kunnen parameters bevatten met hetzelfde formulier als de naam van een variabele. Bijvoorbeeld:

N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter';

Elke parameter die in @stmt is opgenomen, moet een overeenkomende vermelding bevatten in zowel de lijst met @params parameterdefinities als de lijst met parameterwaarden.

[ @params = ] N'@parameter_namedata_type [ ,n ]'

Een tekenreeks die de definities bevat van alle parameters die zijn ingesloten in @stmt. De tekenreeks moet een Unicode-constante of een Unicode-variabele zijn. Elke parameterdefinitie bestaat uit een parameternaam en een gegevenstype. n is een tijdelijke aanduiding die meer parameterdefinities aangeeft. Elke parameter die is opgegeven in @stmt moet worden gedefinieerd in @params. Als de Transact-SQL-instructie of batch in @stmt geen parameters bevat, is @params niet vereist. De standaardwaarde voor deze parameter is NULL.

[ @param1 = ] 'waarde1'

Een waarde voor de eerste parameter die is gedefinieerd in de parametertekenreeks. De waarde kan een Unicode-constante of een Unicode-variabele zijn. Er moet een parameterwaarde zijn opgegeven voor elke parameter die is opgenomen in @stmt. De waarden zijn niet vereist wanneer de Transact-SQL instructie of batch in @stmt geen parameters heeft.

{ OUT | UITVOER }

Geeft aan dat de parameter een uitvoerparameter is. tekst, ntexten afbeelding kunnen parameters worden gebruikt als OUTPUT parameters, tenzij de procedure een algemene CLR-procedure (Language Runtime) is. Een uitvoerparameter die gebruikmaakt van het trefwoord OUTPUT kan een tijdelijke aanduiding voor de cursor zijn, tenzij de procedure een CLR-procedure is.

[ ... n ]

Een tijdelijke aanduiding voor de waarden van extra parameters. Waarden kunnen alleen constanten of variabelen zijn. Waarden kunnen geen complexere expressies zijn, zoals functies of expressies die zijn gebouwd met behulp van operators.

Codewaarden retourneren

0 (geslaagd) of niet-nul (fout).

Resultatenset

Retourneert de resultatensets van alle SQL-instructies die zijn ingebouwd in de SQL-tekenreeks.

Opmerkingen

sp_executesql parameters moeten worden ingevoerd in de specifieke volgorde, zoals beschreven in de sectie Syntaxis eerder in dit artikel. Als de parameters niet in de volgorde zijn ingevoerd, treedt er een foutbericht op.

sp_executesql heeft hetzelfde gedrag als EXECUTE met betrekking tot batches, het bereik van namen en databasecontext. De Transact-SQL-instructie of batch in de parameter sp_executesql@stmt wordt pas gecompileerd nadat de sp_executesql-instructie is uitgevoerd. De inhoud van @stmt wordt vervolgens gecompileerd en uitgevoerd als een uitvoeringsplan, gescheiden van het uitvoeringsplan van de batch die sp_executesqlwordt genoemd. De sp_executesql batch kan niet verwijzen naar variabelen die zijn gedeclareerd in de batch die sp_executesqlaanroept. Lokale cursors of variabelen in de sp_executesql batch zijn niet zichtbaar voor de batch die sp_executesqlaanroept. Wijzigingen in databasecontext duren alleen tot het einde van de sp_executesql-instructie.

sp_executesql kan worden gebruikt in plaats van opgeslagen procedures om een Transact-SQL instructie vaak uit te voeren wanneer de wijziging in parameterwaarden in de instructie de enige variatie is. Omdat de Transact-SQL-instructie zelf constant blijft en alleen de parameterwaarden veranderen, gebruikt de SQL Server-queryoptimalisatie waarschijnlijk het uitvoeringsplan dat wordt gegenereerd voor de eerste uitvoering. In dit scenario zijn de prestaties gelijk aan die van een opgeslagen procedure.

Notitie

Gebruik volledig gekwalificeerde objectnamen in de instructietekenreeks om de prestaties te verbeteren.

sp_executesql ondersteunt de instelling van parameterwaarden afzonderlijk van de Transact-SQL tekenreeks, zoals wordt weergegeven in het volgende voorbeeld.

DECLARE @IntVariable AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);

/* Build the SQL string once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.HumanResources.Employee
       WHERE BusinessEntityID = @BusinessEntityID';

SET @ParmDefinition = N'@BusinessEntityID tinyint';

/* Execute the string with the first parameter value. */
SET @IntVariable = 197;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

Uitvoerparameters kunnen ook worden gebruikt met sp_executesql. In het volgende voorbeeld wordt een functie opgehaald uit de HumanResources.Employee tabel in de AdventureWorks2022 voorbeelddatabase en wordt deze geretourneerd in de uitvoerparameter @max_title.

DECLARE @IntVariable AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS VARCHAR (30);

SET @IntVariable = 197;

SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
   FROM AdventureWorks2022.HumanResources.Employee
   WHERE BusinessEntityID = @level';

SET @ParmDefinition = N'@level TINYINT, @max_titleOUT VARCHAR(30) OUTPUT';

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @level = @IntVariable,
    @max_titleOUT = @max_title OUTPUT;

SELECT @max_title;

Het kunnen vervangen van parameters in sp_executesql biedt de volgende voordelen ten opzichte van het gebruik van de EXECUTE-instructie om een tekenreeks uit te voeren:

• Omdat de werkelijke tekst van de Transact-SQL-instructie in de sp_executesql tekenreeks niet verandert tussen uitvoeringen, komt de queryoptimalisatie waarschijnlijk overeen met de Transact-SQL-instructie in de tweede uitvoering met het uitvoeringsplan dat is gegenereerd voor de eerste uitvoering. Daarom hoeft SQL Server de tweede instructie niet te compileren.

• De Transact-SQL tekenreeks is slechts één keer gemaakt.

• De parameter geheel getal wordt opgegeven in de systeemeigen indeling. Casten naar Unicode is niet vereist.

OPTIMIZED_SP_EXECUTESQL

van toepassing op: Azure SQL Database

Wanneer de OPTIMIZED_SP_EXECUTESQL databaseconfiguratie is ingeschakeld, wordt het compilatiegedrag van batches die worden verzonden met sp_executesql identiek aan het geserialiseerde compilatiegedrag dat objecten zoals opgeslagen procedures en triggers momenteel gebruiken.

Wanneer batches identiek zijn (met uitzondering van eventuele parameterverschillen), probeert de OPTIMIZED_SP_EXECUTESQL optie een compilatievergrendeling te verkrijgen als een afdwingingsmechanisme om te garanderen dat het compilatieproces wordt geserialiseerd. Deze vergrendeling zorgt ervoor dat als meerdere sessies tegelijkertijd sp_executesql aanroepen, deze sessies wachten terwijl ze een exclusieve compileervergrendeling proberen te verkrijgen nadat de eerste sessie het compilatieproces heeft gestart. De eerste uitvoering van sp_executesql compileert en voegt het gecompileerde plan in de plancache in. Andere sessies afbreken wachten op de compile-vergrendeling en hergebruiken het plan zodra het beschikbaar is.

Zonder de optie OPTIMIZED_SP_EXECUTESQL worden meerdere aanroepen van identieke batches uitgevoerd via sp_executesql parallel gecompileerd en in sommige gevallen hun eigen kopieën van een gecompileerd plan in de plancache geplaatst, die in sommige gevallen vermeldingen in de plancache vervangen of dupliceren.

Notitie

Voordat u de configuratie van OPTIMIZED_SP_EXECUTESQL databasebereik inschakelt, moet u, als automatische updatestatistieken is ingeschakeld, ook de asynchrone optie voor het automatisch bijwerken van statistieken inschakelen met de configuratieoptie ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY databasebereik. Het inschakelen van deze twee opties kan de kans aanzienlijk verminderen dat prestatieproblemen met betrekking tot lange compilatietijden samen met overmatige, lock manager exclusieve vergrendelingen (LCK_M_X) en WAIT_ON_SYNC_STATISTICS_REFRESH wachttijden.

OPTIMIZED_SP_EXECUTESQL is standaard uitgeschakeld. Als u OPTIMIZED_SP_EXECUTESQL wilt inschakelen op databaseniveau, gebruikt u de volgende Transact-SQL-instructie:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Machtigingen

Vereist lidmaatschap van de openbare rol.

Voorbeelden

Een. Een SELECT-instructie uitvoeren

In het volgende voorbeeld wordt een SELECT-instructie gemaakt en uitgevoerd die een ingesloten parameter bevat met de naam @level.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. Een dynamisch gebouwde tekenreeks uitvoeren

In het volgende voorbeeld ziet u hoe u sp_executesql gebruikt om een dynamisch gebouwde tekenreeks uit te voeren. De opgeslagen voorbeeldprocedure wordt gebruikt om gegevens in te voegen in een set tabellen die worden gebruikt om verkoopgegevens voor een jaar te partitioneren. Er is één tabel voor elke maand van het jaar met de volgende indeling:

CREATE TABLE May1998Sales
(
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NULL CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth INT CHECK (OrderMonth = 5),
    DeliveryDate DATETIME NULL,
    CHECK (DATEPART(mm, OrderDate) = OrderMonth)
);

Met deze opgeslagen voorbeeldprocedure wordt dynamisch een INSERT instructie gemaakt en uitgevoerd om nieuwe orders in de juiste tabel in te voegen. In het voorbeeld wordt de orderdatum gebruikt om de naam van de tabel te maken die de gegevens moet bevatten en die naam vervolgens op te nemen in een INSERT instructie.

Notitie

Dit is een basisvoorbeeld voor sp_executesql. Het voorbeeld bevat geen foutcontrole en bevat geen controles voor bedrijfsregels, zoals het garanderen dat ordernummers niet tussen tabellen worden gedupliceerd.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS INT;
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
    /* Build the name of the table. */
    SUBSTRING(DATENAME(mm, @PrmOrderDate), 1, 3) +
    CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4)) + 'Sales' +
    /* Build a VALUES clause. */
    ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
    ' @InsOrdMonth, @InsDelDate)';

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate);

EXEC sp_executesql @InsertString,
    N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
    @PrmOrderID,
    @PrmCustomerID,
    @PrmOrderDate,
    @OrderMonth,
    @PrmDeliveryDate;
GO

Het gebruik van sp_executesql in deze procedure is efficiënter dan het gebruik van EXECUTE om de dynamisch gebouwde tekenreeks uit te voeren, omdat hiermee parametermarkeringen kunnen worden gebruikt. Met parametermarkeringen wordt de kans groter dat de database-engine het gegenereerde queryplan hergebruikt, waardoor extra querycompilaties worden voorkomen. Met EXECUTEis elke INSERT tekenreeks uniek omdat de parameterwaarden verschillen en aan het einde van de dynamisch gegenereerde tekenreeks worden toegevoegd. Wanneer de query wordt uitgevoerd, wordt de query niet geparameteriseerd op een manier die het hergebruik van plannen stimuleert en moet worden gecompileerd voordat elke INSERT-instructie wordt uitgevoerd, waardoor een afzonderlijke vermelding in de cache van de query in de plancache wordt toegevoegd.

C. De parameter OUTPUT gebruiken

In het volgende voorbeeld wordt een OUTPUT parameter gebruikt om de resultatenset op te slaan die is gegenereerd door de SELECT-instructie in de parameter @SQLString. Vervolgens worden twee SELECT-instructies uitgevoerd die gebruikmaken van de waarde van de parameter OUTPUT.

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS INT;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';

SET @ParmDefinition = N'@CustomerID INT,
    @SalesOrderOUT NVARCHAR(25) OUTPUT';

SET @IntVariable = 22276;

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @CustomerID = @IntVariable,
    @SalesOrderOUT = @SalesOrderNumber OUTPUT;

-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;

-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate,
       TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

Voorbeelden: Azure Synapse Analytics and Analytics Platform System (PDW)

D. Een SELECT-instructie uitvoeren

In het volgende voorbeeld wordt een SELECT-instructie gemaakt en uitgevoerd die een ingesloten parameter bevat met de naam @level.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
    WHERE EmployeeKey = @level',
    N'@level TINYINT',
    @level = 109;

Verwante inhoud

• EXECUTE (Transact-SQL)
• systeem opgeslagen procedures (Transact-SQL)