Dela via

sp_executesql (Transact-SQL)

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft Fabric

Kör en Transact-SQL-instruktion eller batch som kan återanvändas många gånger, eller en som skapas dynamiskt. Transact-SQL-instruktionen eller batchen kan innehålla inbäddade parametrar.

Försiktighet

Runtime-kompilerade Transact-SQL-instruktioner kan exponera program för skadliga attacker. Du bör parametrisera dina frågor när du använder sp_executesql. Mer information finns i SQL-inmatning.

Transact-SQL syntaxkonventioner

Syntax

Syntax för SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics och Analytics Platform System (PDW).

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

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

Argument

Viktig

Argument för utökade lagrade procedurer måste anges i den specifika ordning som beskrivs i avsnittet Syntax. Om parametrarna anges i fel ordning visas ett felmeddelande.

[ @stmt = ] N"-instruktion"

En Unicode-sträng som innehåller en Transact-SQL-instruktion eller batch. @stmt måste vara antingen en Unicode-konstant eller en Unicode-variabel. Mer komplexa Unicode-uttryck, till exempel att sammanfoga två strängar med +-operatorn, tillåts inte. Teckenkonstanter tillåts inte. Unicode-konstanter måste vara prefix med en N. Till exempel är Unicode-konstanten N'sp_who' giltig, men teckenkonstanten 'sp_who' är inte det. Strängens storlek begränsas endast av tillgängligt databasserverminne. På 64-bitarsservrar är storleken på strängen begränsad till 2 GB, den maximala storleken på nvarchar(max).

@stmt kan innehålla parametrar som har samma formulär som ett variabelnamn. Till exempel:

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

Varje parameter som ingår i @stmt måste ha en motsvarande post i både @params parameterdefinitionslistan och parametervärdena.

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

En sträng som innehåller definitionerna för alla parametrar som är inbäddade i @stmt. Strängen måste vara antingen en Unicode-konstant eller en Unicode-variabel. Varje parameterdefinition består av ett parameternamn och en datatyp. n är en platshållare som anger fler parameterdefinitioner. Varje parameter som anges i @stmt måste definieras i @params. Om Transact-SQL-instruktionen eller batchen i @stmt inte innehåller parametrar krävs inte @params. Standardvärdet för den här parametern är NULL.

[ @param1 = ] "value1"

Ett värde för den första parametern som definieras i parametersträngen. Värdet kan vara en Unicode-konstant eller en Unicode-variabel. Ett parametervärde måste anges för varje parameter som ingår i @stmt. Värdena krävs inte när Transact-SQL-instruktionen eller batchen i @stmt inte har några parametrar.

{ OUT | UTDATA }

Anger att parametern är en utdataparameter. text, ntextoch bild parametrar kan användas som OUTPUT parametrar, såvida inte proceduren är en clr-procedur (common language runtime). En utdataparameter som använder nyckelordet OUTPUT kan vara en markörplatshållare, såvida inte proceduren är en CLR-procedur.

[ ... n ]

En platshållare för värdena för extra parametrar. Värden kan bara vara konstanter eller variabler. Värden kan inte vara mer komplexa uttryck som funktioner eller uttryck som skapas med hjälp av operatorer.

Returnera kodvärden

0 (lyckades) eller icke-noll (fel).

Resultatuppsättning

Returnerar resultatuppsättningarna från alla SQL-instruktioner som är inbyggda i SQL-strängen.

Anmärkningar

sp_executesql parametrar måste anges i den specifika ordning som beskrivs i avsnittet Syntax tidigare i den här artikeln. Om parametrarna anges i fel ordning visas ett felmeddelande.

sp_executesql har samma beteende som EXECUTE när det gäller batchar, namnomfång och databaskontext. Transact-SQL-instruktionen eller batchen i parametern sp_executesql@stmt kompileras inte förrän sp_executesql-instruktionen har körts. Innehållet i @stmt kompileras och körs sedan som en körningsplan separat från körningsplanen för batchen som anropade sp_executesql. Den sp_executesql batchen kan inte referera till variabler som deklarerats i batchen som anropar sp_executesql. Lokala markörer eller variabler i den sp_executesql batchen är inte synliga för batchen som anropar sp_executesql. Ändringar i databaskontexten varar endast i slutet av sp_executesql-instruktionen.

sp_executesql kan användas i stället för lagrade procedurer för att köra en Transact-SQL-instruktion många gånger när ändringen i parametervärden till -instruktionen är den enda varianten. Eftersom själva Transact-SQL-instruktionen förblir konstant och endast parametervärdena ändras, kommer SQL Server-frågeoptimeraren sannolikt att återanvända körningsplanen som genereras för den första körningen. I det här scenariot motsvarar prestandan en lagrad procedur.

Not

För att förbättra prestandan använder du fullständigt kvalificerade objektnamn i instruktionssträngen.

sp_executesql stöder inställningen av parametervärden separat från Transact-SQL strängen, enligt följande exempel.

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;

Utdataparametrar kan också användas med sp_executesql. I följande exempel hämtas en jobbtitel från tabellen HumanResources.Employee i AdventureWorks2022-exempeldatabasen och returnerar den i utdataparametern @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;

Att kunna ersätta parametrar i sp_executesql ger följande fördelar jämfört med att använda EXECUTE-instruktionen för att köra en sträng:

  • Eftersom den faktiska texten i Transact-SQL-instruktionen i sp_executesql strängen inte ändras mellan körningar matchar frågeoptimeraren förmodligen Transact-SQL-instruktionen i den andra körningen med körningsplanen som genererades för den första körningen. Därför behöver SQL Server inte kompilera den andra instruktionen.

  • Strängen Transact-SQL skapas bara en gång.

  • Heltalsparametern anges i sitt interna format. Gjutning till Unicode krävs inte.

OPTIMIZED_SP_EXECUTESQL

gäller för: Azure SQL Database

När den OPTIMIZED_SP_EXECUTESQL databasomfattande konfigurationen är aktiverad blir kompileringsbeteendet för batchar som skickas med sp_executesql identiskt med det serialiserade kompileringsbeteende som objekt som lagrade procedurer och utlösare för närvarande använder.

När batchar är identiska (exklusive eventuella parameterskillnader) försöker alternativet OPTIMIZED_SP_EXECUTESQL hämta ett kompileringslås som en tillämpningsmekanism för att garantera att kompileringsprocessen serialiseras. Det här låset säkerställer att om flera sessioner anropar sp_executesql samtidigt väntar dessa sessioner medan de försöker få ett exklusivt kompileringslås efter att den första sessionen har startat kompileringsprocessen. Den första körningen av sp_executesql kompilerar och infogar sin kompilerade plan i plancachen. Andra sessioner avbryter väntan på kompileringslåset och återanvänder planen när den blir tillgänglig.

Utan alternativet OPTIMIZED_SP_EXECUTESQL flera anrop av identiska batchar som körs via sp_executesql kompileras parallellt och placera sina egna kopior av en kompilerad plan i plancachen, som i vissa fall ersätter eller duplicerar poster för plancache.

Not

Om automatisk uppdateringsstatistik är aktiverad innan du aktiverar OPTIMIZED_SP_EXECUTESQL databasomfattning bör du även aktivera alternativet asynkron automatisk uppdateringsstatistik med konfigurationsalternativet ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY databasomfattning. Om du aktiverar dessa två alternativ kan du avsevärt minska sannolikheten för att prestandaproblem relaterade till långa kompileringstider tillsammans med överdrivna, lås manager-exklusiva lås (LCK_M_X) och WAIT_ON_SYNC_STATISTICS_REFRESH väntetider.

OPTIMIZED_SP_EXECUTESQL är inaktiverat som standard. Om du vill aktivera OPTIMIZED_SP_EXECUTESQL på databasnivå använder du följande Transact-SQL-instruktion:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Behörigheter

Kräver medlemskap i den offentliga rollen.

Exempel

A. Köra en SELECT-instruktion

I följande exempel skapas och körs en SELECT-instruktion som innehåller en inbäddad parameter med namnet @level.

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

B. Köra en dynamiskt byggd sträng

I följande exempel visas hur du använder sp_executesql för att köra en dynamiskt byggd sträng. Den lagrade exempelproceduren används för att infoga data i en uppsättning tabeller som används för att partitionera försäljningsdata under ett år. Det finns en tabell för varje månad på året som har följande format:

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

Den här lagrade exempelproceduren skapar och kör dynamiskt en INSERT-instruktion för att infoga nya order i rätt tabell. I exemplet används orderdatumet för att skapa namnet på tabellen som ska innehålla data och införlivar sedan namnet i en INSERT-instruktion.

Not

Det här är ett grundläggande exempel för sp_executesql. Exemplet innehåller inte felkontroll och innehåller inte kontroller för affärsregler, till exempel att garantera att ordernummer inte dupliceras mellan tabeller.

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

Att använda sp_executesql i den här proceduren är effektivare än att använda EXECUTE för att köra den dynamiskt byggda strängen, eftersom den tillåter användning av parametermarkörer. Parametermarkörer gör det mer troligt att databasmotorn återanvänder den genererade frågeplanen, vilket hjälper till att undvika ytterligare frågekompileringar. Med EXECUTEär varje INSERT sträng unik eftersom parametervärdena är olika och läggs till i slutet av den dynamiskt genererade strängen. När frågan körs skulle den inte parametriseras på ett sätt som uppmuntrar till återanvändning av planen och måste kompileras innan varje INSERT-instruktion körs, vilket skulle lägga till en separat cachelagrad post för frågan i plancachen.

C. Använda parametern OUTPUT

I följande exempel används en OUTPUT-parameter för att lagra resultatuppsättningen som genereras av SELECT-instruktionen i parametern @SQLString. Två SELECT-instruktioner körs sedan som använder värdet för parametern 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;

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

D. Köra en SELECT-instruktion

I följande exempel skapas och körs en SELECT-instruktion som innehåller en inbäddad parameter med namnet @level.

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

Relaterat innehåll