sp_executesql (Transact-SQL)

Článek
03/11/2025

platí pro:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform Platform System (PDW)koncový bod SQL Analytics ve službě Microsoft Fabric Warehouse v Microsoft Fabric

Spustí příkaz Transact-SQL nebo dávku, které lze opakovaně použít, nebo příkaz, který se sestavuje dynamicky. Příkaz Transact-SQL nebo dávka mohou obsahovat vložené parametry.

Opatrnost

Příkazy Transact-SQL kompilované za běhu můžou vystavit aplikace škodlivým útokům. Při použití sp_executesqlbyste měli parametrizovat dotazy . Další informace naleznete v tématu injektáž SQL.

Transact-SQL konvence syntaxe

Syntax

Syntaxe pro SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics a systém PDW (Analytics Platform System).

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

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky ukázky a komunitní projekty Microsoft SQL Serveru.

Argumenty

Důležitý

Argumenty rozšířených uložených procedur musí být zadány v určitém pořadí, jak je popsáno v části Syntaxe. Pokud jsou parametry zadány mimo pořadí, dojde k chybové zprávě.

[ @stmt = ] Příkaz N''

Řetězec Unicode, který obsahuje příkaz Transact-SQL nebo dávku. @stmt musí být buď konstanta Unicode, nebo proměnná Unicode. Složitější výrazy Unicode, jako je zřetězení dvou řetězců pomocí operátoru +, nejsou povolené. Znakové konstanty nejsou povoleny. Konstanty Unicode musí mít předponu N. Například konstanta Unicode N'sp_who' je platná, ale znaková konstanta 'sp_who' není. Velikost řetězce je omezená pouze dostupnou pamětí databázového serveru. Na 64bitových serverech je velikost řetězce omezená na 2 GB, maximální velikost nvarchar(max).

@stmt mohou obsahovat parametry se stejným formulářem jako název proměnné. Například:

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

Každý parametr zahrnutý v @stmt musí mít odpovídající položku v seznamu definic parametrů @params i v seznamu hodnot parametrů.

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

Řetězec, který obsahuje definice všech parametrů, které jsou vloženy do @stmt. Řetězec musí být buď konstanta Unicode, nebo proměnná Unicode. Každá definice parametru se skládá z názvu parametru a datového typu. n je zástupný symbol označující další definice parametrů. Každý parametr zadaný v @stmt musí být definován v @params. Pokud příkaz Transact-SQL nebo dávka v @stmt neobsahuje parametry, @params se nevyžaduje. Výchozí hodnota tohoto parametru je NULL.

[ @param1 = ] 'hodnota1'

Hodnota prvního parametru, který je definován v řetězci parametru. Hodnota může být konstanta Unicode nebo proměnná Unicode. Musí existovat hodnota parametru zadaná pro každý parametr zahrnutý v @stmt. Hodnoty nejsou vyžadovány, pokud příkaz Transact-SQL nebo dávka v @stmt nemá žádné parametry.

{ OUT | OUTPUT }

Označuje, že parametr je výstupní parametr. textové, ntextovéa parametry obrázku se dají použít jako parametry OUTPUT, pokud se nejedná o proceduru CLR (Common Language Runtime). Výstupní parametr, který používá klíčové slovo OUTPUT, může být zástupný symbol kurzoru, pokud se nejedná o proceduru CLR.

[ ... n ]

Zástupný symbol pro hodnoty dodatečných parametrů. Hodnoty mohou být pouze konstanty nebo proměnné. Hodnoty nemohou být složitější výrazy, jako jsou funkce nebo výrazy vytvořené pomocí operátorů.

Návratové hodnoty kódu

0 (úspěch) nebo nenulová (selhání).

Sada výsledků

Vrátí sady výsledků ze všech příkazů SQL integrovaných do řetězce SQL.

Poznámky

sp_executesql parametry musí být zadány v konkrétním pořadí, jak je popsáno v části syntaxe výše v tomto článku. Pokud jsou parametry zadány mimo pořadí, dojde k chybové zprávě.

sp_executesql má stejné chování jako EXECUTE týkající se dávek, rozsahu názvů a kontextu databáze. Příkaz Transact-SQL nebo dávka v parametru sp_executesql@stmt se nezkompiluje, dokud se nespustí příkaz sp_executesql. Obsah @stmt se pak zkompiluje a spustí jako plán provádění odděleně od plánu provádění dávky, která se nazývá sp_executesql. Dávka sp_executesql nemůže odkazovat na proměnné deklarované v dávce, která volá sp_executesql. Místní kurzory nebo proměnné v dávce sp_executesql nejsou viditelné pro dávku, která volá sp_executesql. Změny v kontextu databáze jsou poslední až na konec příkazu sp_executesql.

sp_executesql lze použít místo uložených procedur k provádění příkazu Transact-SQL mnohokrát, když změna hodnot parametrů na příkaz je jedinou variantou. Vzhledem k tomu, že samotný příkaz Transact-SQL zůstává konstantní a změní se pouze hodnoty parametrů, je pravděpodobné, že optimalizátor dotazů SQL Serveru znovu použije plán provádění, který vygeneruje pro první spuštění. V tomto scénáři je výkon ekvivalentní výkonu uložené procedury.

Poznámka

Pokud chcete zvýšit výkon, použijte v řetězci příkazu plně kvalifikované názvy objektů.

sp_executesql podporuje nastavení hodnot parametrů odděleně od řetězce Transact-SQL, jak je znázorněno v následujícím příkladu.

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;

Výstupní parametry lze použít také s sp_executesql. Následující příklad načte název úlohy z tabulky HumanResources.Employee v ukázkové databázi AdventureWorks2022 a vrátí ji ve výstupním parametru @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;

Možnost nahradit parametry v sp_executesql nabízí následující výhody oproti použití příkazu EXECUTE ke spuštění řetězce:

Vzhledem k tomu, že skutečný text příkazu Transact-SQL v řetězci sp_executesql se mezi spuštěními nezmění, optimalizátor dotazů pravděpodobně odpovídá příkazu Transact-SQL ve druhém spuštění s plánem provádění vygenerovaným pro první spuštění. Sql Server proto nemusí kompilovat druhý příkaz.
Řetězec Transact-SQL je sestaven pouze jednou.
Celočíselná hodnota parametru je zadána v nativním formátu. Přetypování na Unicode není povinné.

OPTIMIZED_SP_EXECUTESQL

platí pro: Azure SQL Database

Pokud je povolena konfigurace s oborem OPTIMIZED_SP_EXECUTESQL databáze, chování kompilace dávek odeslaných pomocí sp_executesql bude stejné jako serializované chování kompilace, které objekty, jako jsou uložené procedury a triggery aktuálně používají.

Pokud jsou dávky identické (s výjimkou rozdílů parametrů), OPTIMIZED_SP_EXECUTESQL možnost se pokusí získat zámek kompilace jako mechanismus vynucení, aby se zajistilo serializace procesu kompilace. Tento zámek zajistí, že pokud více relací vyvolá sp_executesql současně, budou tyto relace čekat při pokusu o získání výhradního zámku kompilace po spuštění procesu kompilace první relace. První spuštění sp_executesql zkompiluje a vloží jeho zkompilovaný plán do mezipaměti plánu. Jiné relace přeruší čekání na zámek kompilace a znovu použije plán, jakmile bude k dispozici.

Bez možnosti OPTIMIZED_SP_EXECUTESQL se několik vyvolání identických dávek spouštěných paralelně sp_executesql kompilace a umístění vlastních kopií zkompilovaného plánu do mezipaměti plánu, které v některých případech nahrazují nebo duplikují položky mezipaměti plánu.

Poznámka

Před povolením konfigurace s oborem databáze OPTIMIZED_SP_EXECUTESQL, pokud je povolená statistika automatické aktualizace, měli byste také povolit asynchronní možnost automatické aktualizace statistiky s možností konfigurace s oborem ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY databáze. Povolení těchto dvou možností může výrazně snížit pravděpodobnost, že problémy s výkonem související s dlouhými dobami kompilace spolu s nadměrnými zámky (LCK_M_X) a WAIT_ON_SYNC_STATISTICS_REFRESH čekání.

OPTIMIZED_SP_EXECUTESQL je ve výchozím nastavení vypnuté. Pokud chcete povolit OPTIMIZED_SP_EXECUTESQL na úrovni databáze, použijte následující příkaz Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Dovolení

Vyžaduje členství ve veřejné roli.

Příklady

A. Provedení příkazu SELECT

Následující příklad vytvoří a spustí příkaz SELECT, který obsahuje vložený parametr s názvem @level.

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

B. Spuštění dynamicky vytvořeného řetězce

Následující příklad ukazuje použití sp_executesql ke spuštění dynamicky sestaveného řetězce. Příklad uložené procedury slouží k vložení dat do sady tabulek, které slouží k rozdělení prodejních dat za rok. Pro každý měsíc v roce existuje jedna tabulka, která má následující formát:

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

Tato ukázková uložená procedura dynamicky sestaví a spustí příkaz INSERT, který vloží nové objednávky do správné tabulky. Příklad používá datum objednávky k sestavení názvu tabulky, která by měla obsahovat data, a pak tento název začlení do příkazu INSERT.

Poznámka

Toto je základní příklad pro sp_executesql. Příklad neobsahuje kontrolu chyb a nezahrnuje kontroly obchodních pravidel, jako je například záruka, že čísla objednávek nejsou mezi tabulkami duplicitní.

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

Použití sp_executesql v tomto postupu je efektivnější než použití EXECUTE ke spuštění dynamicky sestaveného řetězce, protože umožňuje použití značek parametrů. Značky parametrů umožňují větší pravděpodobnost, že databázový stroj opakovaně používá vygenerovaný plán dotazů, což pomáhá vyhnout se dalším kompilacemi dotazů. U EXECUTEje každý řetězec INSERT jedinečný, protože hodnoty parametrů se liší a připojí se ke konci dynamicky generovaného řetězce. Při spuštění by dotaz nebyl parametrizován způsobem, který podporuje opakované použití plánu a musel by být zkompilován před provedením každého příkazu INSERT, což by přidalo samostatnou položku dotazu uloženou v mezipaměti plánu do mezipaměti.

C. Použití parametru OUTPUT

Následující příklad používá parametr OUTPUT k uložení sady výsledků vygenerované příkazem SELECT v parametru @SQLString. Pak se spustí dva příkazy SELECT, které používají hodnotu parametru 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;

Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)

D. Provedení příkazu SELECT

Následující příklad vytvoří a spustí příkaz SELECT, který obsahuje vložený parametr s názvem @level.

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

EXECUTE (Transact-SQL)
uložené procedury systému (Transact-SQL)

Sdílet prostřednictvím

sp_executesql (Transact-SQL)

Syntax

Argumenty

[ @stmt = ] Příkaz N''

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

[ @param1 = ] 'hodnota1'

{ OUT | OUTPUT }

[ ... n ]

Návratové hodnoty kódu

Sada výsledků

Poznámky

OPTIMIZED_SP_EXECUTESQL

Dovolení

Příklady

A. Provedení příkazu SELECT

B. Spuštění dynamicky vytvořeného řetězce

C. Použití parametru OUTPUT

Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)

D. Provedení příkazu SELECT

Váš názor

Další materiály

Sdílet prostřednictvím

sp_executesql (Transact-SQL)

Syntax

Argumenty

[ @stmt = ] Příkaz N''

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

[ @param1 = ] 'hodnota1'

{ OUT | OUTPUT }

[ ... n ]

Návratové hodnoty kódu

Sada výsledků

Poznámky

OPTIMIZED_SP_EXECUTESQL

Dovolení

Příklady

A. Provedení příkazu SELECT

B. Spuštění dynamicky vytvořeného řetězce

C. Použití parametru OUTPUT

Příklady: Azure Synapse Analytics a Analytický platformový systém (PDW)

D. Provedení příkazu SELECT

Související obsah

Váš názor

Další materiály