Udostępnij za pośrednictwem

sp_executesql (Transact-SQL)

  • Artykuł

Dotyczy:programu SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)punkt końcowy analizy SQL w usłudze Microsoft FabricWarehouse w usłudze Microsoft Fabric

Wykonuje instrukcję Transact-SQL lub partię, którą można wielokrotnie używać lub jedną, która jest tworzona dynamicznie. Instrukcja Transact-SQL lub partia może zawierać osadzone parametry.

Ostrożność

Kompilowane w czasie wykonywania instrukcje Transact-SQL mogą uwidaczniać aplikacje złośliwym atakom. Podczas korzystania z sp_executesqlnależy sparametryzować zapytania. Aby uzyskać więcej informacji, zobacz iniekcji SQL.

Transact-SQL konwencje składni

Składnia

Składnia dla programów SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics i Analytics Platform System (PDW).

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

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

Argumenty

Ważny

Argumenty dla rozszerzonych procedur składowanych należy wprowadzić w określonej kolejności zgodnie z opisem w sekcji składni. Jeśli parametry są wprowadzane poza kolejnością, wystąpi komunikat o błędzie.

[ @stmt = ] N', instrukcja"

Ciąg Unicode zawierający instrukcję Transact-SQL lub partię. @stmt musi być stałą Unicode lub zmienną Unicode. Bardziej złożone wyrażenia Unicode, takie jak łączenie dwóch ciągów za pomocą operatora +, nie są dozwolone. Stałe znaków nie są dozwolone. Stałe Unicode muszą być poprzedzone N. Na przykład stała Unicode N'sp_who' jest prawidłowa, ale stała znaku 'sp_who' nie jest. Rozmiar ciągu jest ograniczony tylko przez dostępną pamięć serwera bazy danych. Na serwerach 64-bitowych rozmiar ciągu jest ograniczony do 2 GB, maksymalny rozmiar nvarchar(max).

@stmt może zawierać parametry o takiej samej formie jak nazwa zmiennej. Na przykład:

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

Każdy parametr uwzględniony w @stmt musi mieć odpowiedni wpis na liście definicji parametrów @params i na liście wartości parametrów.

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

Ciąg zawierający definicje wszystkich parametrów osadzonych w @stmt. Ciąg musi być stałą Unicode lub zmienną Unicode. Każda definicja parametru składa się z nazwy parametru i typu danych. n jest symbolem zastępczym wskazującym więcej definicji parametrów. Każdy parametr określony w @stmt musi być zdefiniowany w @params. Jeśli instrukcja Transact-SQL lub partia w @stmt nie zawiera parametrów, @params nie jest wymagana. Wartość domyślna tego parametru to NULL.

[ @param1 = ] 'wartość1'

Wartość pierwszego parametru zdefiniowanego w ciągu parametru. Wartość może być stałą Unicode lub zmienną Unicode. Dla każdego parametru uwzględnionego w @stmtmusi być podana wartość parametru . Wartości nie są wymagane, gdy instrukcja Transact-SQL lub partia w @stmt nie ma parametrów.

{ OUT | DANE WYJŚCIOWE }

Wskazuje, że parametr jest parametrem wyjściowym. tekstu , ntexti parametry obrazu mogą być używane jako parametry OUTPUT, chyba że procedura jest procedurą środowiska uruchomieniowego języka wspólnego (CLR). Parametr wyjściowy używający słowa kluczowego OUTPUT może być symbolem zastępczym kursora, chyba że procedura jest procedurą CLR.

[ ... n ]

Symbol zastępczy wartości dodatkowych parametrów. Wartości mogą być tylko stałymi lub zmiennymi. Wartości nie mogą być bardziej złożonymi wyrażeniami, takimi jak funkcje lub wyrażenia utworzone przy użyciu operatorów.

Zwracanie wartości kodu

0 (powodzenie) lub niezerowe (niepowodzenie).

Zestaw wyników

Zwraca zestawy wyników ze wszystkich instrukcji SQL wbudowanych w ciąg SQL.

Uwagi

sp_executesql parametry należy wprowadzić w określonej kolejności zgodnie z opisem w sekcji Składnia wcześniej w tym artykule. Jeśli parametry są wprowadzane poza kolejnością, wystąpi komunikat o błędzie.

sp_executesql ma takie samo zachowanie jak EXECUTE dotyczące partii, zakresu nazw i kontekstu bazy danych. Instrukcja Transact-SQL lub partia w parametrze sp_executesql@stmt nie jest kompilowana do momentu wykonania instrukcji sp_executesql. Zawartość @stmt jest następnie kompilowana i wykonywana jako plan wykonania oddzielnie od planu wykonania partii o nazwie sp_executesql. Partia sp_executesql nie może odwoływać się do zmiennych zadeklarowanych w partii, która wywołuje sp_executesql. Lokalne kursory lub zmienne w partii sp_executesql nie są widoczne dla partii, która wywołuje sp_executesql. Zmiany w kontekście bazy danych trwają tylko na końcu instrukcji sp_executesql.

sp_executesql można użyć zamiast procedur składowanych do wykonywania instrukcji Transact-SQL wiele razy, gdy zmiana wartości parametrów na instrukcję jest jedyną odmianą. Ponieważ sama instrukcja Transact-SQL pozostaje stała i zmienia się tylko wartości parametrów, optymalizator zapytań programu SQL Server może ponownie użyć planu wykonywania, który generuje na potrzeby pierwszego wykonania. W tym scenariuszu wydajność jest równoważna wydajności procedury składowanej.

Nuta

Aby zwiększyć wydajność, użyj w pełni kwalifikowanych nazw obiektów w ciągu instrukcji.

sp_executesql obsługuje ustawienie wartości parametrów oddzielnie od ciągu Transact-SQL, jak pokazano w poniższym przykładzie.

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;

Parametry wyjściowe mogą być również używane z sp_executesql. Poniższy przykład pobiera tytuł zadania z tabeli HumanResources.Employee w przykładowej bazie danych AdventureWorks2022 i zwraca go w parametrze wyjściowym @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żliwość zastąpienia parametrów w sp_executesql oferuje następujące korzyści w porównaniu z użyciem instrukcji EXECUTE do wykonania ciągu:

  • Ponieważ rzeczywisty tekst instrukcji Transact-SQL w ciągu sp_executesql nie zmienia się między wykonaniami, optymalizator zapytań prawdopodobnie pasuje do instrukcji Transact-SQL w drugim wykonaniu z planem wykonywania wygenerowany na potrzeby pierwszego wykonania. W związku z tym program SQL Server nie musi kompilować drugiej instrukcji.

  • Ciąg Transact-SQL jest kompilowany tylko raz.

  • Parametr liczby całkowitej jest określony w formacie natywnym. Rzutowanie do formatu Unicode nie jest wymagane.

OPTIMIZED_SP_EXECUTESQL

Dotyczy: Azure SQL Database

Po włączeniu konfiguracji w zakresie bazy danych OPTIMIZED_SP_EXECUTESQL zachowanie kompilacji partii przesłanych przy użyciu sp_executesql staje się identyczne z zachowaniem serializacji kompilacji, które są obecnie używane przez obiekty, takie jak procedury składowane i wyzwalacze.

Gdy partie są identyczne (z wyłączeniem wszelkich różnic parametrów), opcja OPTIMIZED_SP_EXECUTESQL próbuje uzyskać blokadę kompilacji jako mechanizm wymuszania w celu zagwarantowania serializacji procesu kompilacji. Ta blokada gwarantuje, że jeśli wiele sesji wywoła sp_executesql jednocześnie, te sesje będą czekać podczas próby uzyskania blokady kompilacji wyłącznej po pierwszym uruchomieniu procesu kompilacji. Pierwsze wykonanie sp_executesql kompiluje i wstawia skompilowany plan do pamięci podręcznej planu. Inne sesje przerywają oczekiwanie na blokadę kompilacji i ponownie użyj planu po udostępnieniu.

Bez opcji OPTIMIZED_SP_EXECUTESQL wiele wywołań identycznych partii wykonywanych za pomocą sp_executesql kompilować równolegle i umieszczać własne kopie skompilowanego planu w pamięci podręcznej planu, które w niektórych przypadkach zastępują lub duplikują wpisy pamięci podręcznej planu.

Nuta

Przed włączeniem konfiguracji w zakresie OPTIMIZED_SP_EXECUTESQL bazy danych, jeśli włączono automatyczne statystyki aktualizacji, należy również włączyć opcję asynchroniczną automatycznej aktualizacji z opcją konfiguracji o zakresie ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY bazy danych. Włączenie tych dwóch opcji może znacząco zmniejszyć prawdopodobieństwo, że problemy z wydajnością związane z długim czasem kompilacji wraz z nadmiernymi blokadami wyłącznymi menedżera blokad (LCK_M_X) i oczekiwaniami WAIT_ON_SYNC_STATISTICS_REFRESH.

OPTIMIZED_SP_EXECUTESQL jest domyślnie wyłączona. Aby włączyć OPTIMIZED_SP_EXECUTESQL na poziomie bazy danych, użyj następującej instrukcji Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Uprawnienia

Wymaga członkostwa w roli publicznej.

Przykłady

A. Wykonywanie instrukcji SELECT

Poniższy przykład tworzy i wykonuje instrukcję SELECT zawierającą osadzony parametr o nazwie @level.

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

B. Wykonywanie dynamicznie utworzonego ciągu

W poniższym przykładzie pokazano użycie sp_executesql do wykonania dynamicznie utworzonego ciągu. Przykładowa procedura składowana służy do wstawiania danych do zestawu tabel używanych do partycjonowania danych sprzedaży przez rok. Istnieje jedna tabela dla każdego miesiąca roku, która ma następujący 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)
);

Ta przykładowa procedura składowana dynamicznie kompiluje i wykonuje instrukcję INSERT, aby wstawić nowe zamówienia do właściwej tabeli. W przykładzie użyto daty zamówienia do skompilowania nazwy tabeli, która powinna zawierać dane, a następnie dołącza tę nazwę do instrukcji INSERT.

Nuta

Jest to podstawowy przykład sp_executesql. Przykład nie zawiera sprawdzania błędów i nie obejmuje sprawdzania reguł biznesowych, takich jak zagwarantowanie, że numery zamówień nie są zduplikowane między tabelami.

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

Użycie sp_executesql w tej procedurze jest bardziej wydajne niż użycie EXECUTE do wykonywania dynamicznie utworzonego ciągu, ponieważ umożliwia użycie znaczników parametrów. Znaczniki parametrów sprawiają, że aparat bazy danych ponownie używa wygenerowanego planu zapytania, co pomaga uniknąć dodatkowych kompilacji zapytań. W przypadku EXECUTEkażdy ciąg INSERT jest unikatowy, ponieważ wartości parametrów są różne i zostaną dołączone na końcu dynamicznie wygenerowanego ciągu. Po wykonaniu zapytanie nie będzie sparametryzowane w sposób, który zachęca do ponownego użycia planu i musiałby zostać skompilowany przed wykonaniem każdej instrukcji INSERT, co spowoduje dodanie oddzielnego buforowanego wpisu zapytania w pamięci podręcznej planu.

C. Używanie parametru OUTPUT

W poniższym przykładzie użyto parametru OUTPUT do przechowywania zestawu wyników wygenerowanego przez instrukcję SELECT w parametrze @SQLString. Następnie są wykonywane dwie instrukcje SELECT, które używają wartości 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;

Przykłady: Azure Synapse Analytics and Analytics Platform System (PDW)

D. Wykonywanie instrukcji SELECT

Poniższy przykład tworzy i wykonuje instrukcję SELECT zawierającą osadzony parametr o nazwie @level.

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

Powiązana zawartość