Teilen über

sp_executesql (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric

Führt eine Transact-SQL-Anweisung oder einen Batch aus, die mehrmals wiederverwendet werden kann, oder eine, die dynamisch erstellt wird. Die Transact-SQL-Anweisung oder der -Batch können eingebettete Parameter enthalten.

Achtung

Laufzeitkompilierte Transact-SQL-Anweisungen können Anwendungen böswilligen Angriffen zur Verfügung stellen. Sie sollten Ihre Abfragen bei Verwendung parametrisieren sp_executesql. Weitere Informationen finden Sie unter Einschleusung von SQL-Befehlen.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL-Datenbank, Azure SQL verwaltete Instanz, Azure Synapse Analytics und Analytics Platform System (PDW).

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

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

Argumente

[ @stmt = ] N'statement'

Eine Unicode-Zeichenfolge, die eine Transact-SQL-Anweisung oder einen Batch enthält. @stmt muss eine Unicode-Konstante oder eine Unicode-Variable sein. Komplexere Unicodeausdrücke, wie z. B. die Verkettung von zwei Zeichenfolgen mit dem +-Operator, sind nicht zulässig. Zeichenkonstanten sind nicht zulässig. Unicode-Konstanten müssen einem NPräfix vorangestellt werden. Die Unicode-Konstante N'sp_who' ist beispielsweise gültig, die Zeichenkonstante 'sp_who' jedoch nicht. Die Länge der Zeichenfolge wird nur durch den verfügbaren Arbeitsspeicher des Datenbankservers begrenzt. Auf 64-Bit-Servern ist die Größe der Zeichenfolge auf 2 GB, die Maximalgröße von nvarchar(max), begrenzt.

@stmt können Parameter enthalten, die dasselbe Formular wie ein Variablenname aufweisen. Zum Beispiel:

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

Für jeden Parameter in @stmt ist ein entsprechender Eintrag in der Parameterdefinitionsliste @params und in der Parameterwerteliste erforderlich.

[ @params = ] N'@parameter_name data_type [ , ...n ]'

Eine Zeichenfolge, die die Definitionen aller Parameter enthält, die in @stmt eingebettet sind. Die Zeichenfolge muss entweder eine Unicode-Konstante oder eine Unicode-Variable sein. Jede Parameterdefinition besteht aus einem Parameternamen und einem Datentyp. n ist ein Platzhalter für mehr Parameterdefinitionen. Jeder in @stmt angegebene Parameter muss in @params definiert werden. Wenn die Transact-SQL-Anweisung oder der Batch in @stmt keine Parameter enthält, ist @params nicht erforderlich. Der Standardwert für diesen Parameter ist NULL.

[ @param1 = ] 'value1'

Ein Wert für den ersten Parameter, der in der Parameterzeichenfolge definiert ist. Bei diesem Wert kann es sich um eine Unicode-Konstante oder eine Unicode-Variable handeln. Für jeden Parameter, der in @stmt enthalten ist, muss ein Parameterwert angegeben werden. Die Werte sind nicht erforderlich, wenn die Transact-SQL-Anweisung oder der Batch in @stmt keine Parameter enthält.

{ OUT | OUTPUT }

Gibt an, dass es sich bei dem Parameter um einen Ausgabeparameter handelt. Text-, ntext- und Bildparameter können als OUTPUT Parameter verwendet werden, es sei denn, die Prozedur ist eine CLR-Prozedur (Common Language Runtime). Ein Ausgabeparameter, der das OUTPUT Schlüsselwort verwendet, kann ein Cursorplatzhalter sein, es sei denn, die Prozedur ist eine CLR-Prozedur.

[ ... n ]

Ein Platzhalter für die Werte der zusätzlichen Parameter. Werte können nur Konstanten oder Variablen sein. Werte können keine komplexeren Ausdrücke sein, wie z. B. Funktionen oder Ausdrücke, die mithilfe von Operatoren erstellt werden.

Rückgabecodewerte

0 (Erfolg) oder ungleich 0 (Fehler).

Resultset

Gibt die Resultsets von allen SQL-Anweisungen der SQL-Zeichenfolge zurück.

Hinweise

sp_executesql Parameter müssen in der spezifischen Reihenfolge eingegeben werden, wie im Abschnitt "Syntax " weiter oben in diesem Artikel beschrieben. Wenn die Parameter nicht in der vorgegebenen Reihenfolge eingegeben werden, wird eine Fehlermeldung ausgegeben.

sp_executesql hat das gleiche Verhalten wie EXECUTE bei Batches, dem Bereich der Namen und dem Datenbankkontext. Die Transact-SQL-Anweisung oder der sp_executesql Batch im @stmt-Parameter wird erst kompiliert, wenn die sp_executesql Anweisung ausgeführt wird. Die Inhalte von @stmt werden dann kompiliert und als Ausführungsplan getrennt vom Ausführungsplan des aufgerufenen sp_executesqlBatches ausgeführt. Der sp_executesql Batch kann nicht auf variablen verweisen, die im Batch deklariert sind, der aufgerufen wird sp_executesql. Lokale Cursor oder Variablen im sp_executesql Batch sind für den Batch, der aufruft sp_executesql, nicht sichtbar. Änderungen am Datenbankkontext sind nur bis zum Ende der sp_executesql -Anweisung gültig.

sp_executesql kann anstelle gespeicherter Prozeduren verwendet werden, um eine Transact-SQL-Anweisung mehrmals auszuführen, wenn die Änderung der Parameterwerte in die Anweisung die einzige Variation ist. Da die Transact-SQL-Anweisung selbst unverändert bleibt und sich nur die Parameterwerte ändern, wird der SQL Server-Abfrageoptimierer wahrscheinlich den Ausführungsplan wiederverwenden, der für die erste Ausführung erstellt wird. In diesem Szenario entspricht die Leistung dem einer gespeicherten Prozedur.

Hinweis

Um die Leistung zu verbessern, verwenden Sie vollqualifizierte Objektnamen in der Anweisungszeichenfolge.

sp_executesql unterstützt die Einstellung von Parameterwerten getrennt von der Transact-SQL-Zeichenfolge, wie im folgenden Beispiel gezeigt.

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;

Ausgabeparameter können auch mit sp_executesql. Im folgenden Beispiel wird eine Position aus der Tabelle in der HumanResources.Employee AdventureWorks2022 Beispieldatenbank abgerufen und im Ausgabeparameter @max_titlezurückgegeben.

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;

sp_executesql Die Verwendung der EXECUTE Anweisung zum Ausführen einer Zeichenfolge bietet folgende Vorteile:

  • Da sich der tatsächliche Text der Transact-SQL-Anweisung in der sp_executesql Zeichenfolge nicht zwischen den Ausführungen ändert, entspricht der Abfrageoptimierer wahrscheinlich der Transact-SQL-Anweisung in der zweiten Ausführung mit dem für die erste Ausführung generierten Ausführungsplan. Daher muss SQL Server die zweite Anweisung nicht kompilieren.

  • Die Transact-SQL-Zeichenfolge wird nur einmal erstellt.

  • Der integer-Parameter wird im systemeigenen Format angegeben. Umwandlung in Unicode ist nicht erforderlich.

OPTIMIZED_SP_EXECUTESQL

Gilt für: Azure SQL-Datenbank

Wenn die konfiguration mit OPTIMIZED_SP_EXECUTESQL Datenbankbereich aktiviert ist, wird das Kompilierungsverhalten der übermittelten Batches mit sp_executesql dem serialisierten Kompilierungsverhalten identisch, das Objekte wie gespeicherte Prozeduren und Trigger derzeit verwenden.

Wenn Batches identisch sind (ohne Parameterunterschiede), versucht die OPTIMIZED_SP_EXECUTESQL Option, eine Kompilierungssperre als Erzwingungsmechanismus abzurufen, um sicherzustellen, dass der Kompilierungsprozess serialisiert wird. Diese Sperre stellt sicher, dass, wenn mehrere Sitzungen gleichzeitig aufgerufen sp_executesql werden, diese Sitzungen warten, während sie versuchen, eine exklusive Kompilierungssperre zu erhalten, nachdem die erste Sitzung den Kompilierungsprozess gestartet hat. Die erste Ausführung der sp_executesql Kompilierung und fügt den kompilierten Plan in den Plancache ein. Andere Sitzungen werden abgebrochen, wenn sie auf die Kompilierungssperre warten und den Plan wiederverwenden, sobald er verfügbar ist.

Ohne die OPTIMIZED_SP_EXECUTESQL Option werden mehrere Aufrufe identischer Batches, die parallel über sp_executesql kompiliert ausgeführt werden, ausgeführt und ihre eigenen Kopien eines kompilierten Plans in den Plancache eingefügt, wodurch in einigen Fällen Einträge des Plancaches ersetzt oder dupliziert werden.

Hinweis

Bevor Sie die OPTIMIZED_SP_EXECUTESQL Konfiguration mit Datenbankbereich aktivieren, sollten Sie, wenn statistiken für automatische Updates aktiviert sind, auch die asynchrone Option für automatische Aktualisierungsstatistiken mit der Option ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY Datenbankbereichskonfiguration aktivieren. Durch das Aktivieren dieser beiden Optionen kann die Wahrscheinlichkeit erheblich reduziert werden, dass Leistungsprobleme im Zusammenhang mit langen Kompilierungszeiten zusammen mit übermäßigen, exklusiven Lock-Manager-Sperren (LCK_M_X) und WAIT_ON_SYNC_STATISTICS_REFRESH Wartezeiten auftreten.

OPTIMIZED_SP_EXECUTESQL ist standardmäßig deaktiviert. Verwenden Sie zum Aktivieren OPTIMIZED_SP_EXECUTESQL auf Datenbankebene die folgende Transact-SQL-Anweisung:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

Berechtigungen

Erfordert die Mitgliedschaft in der public -Rolle.

Beispiele

A. Ausführen einer SELECT-Anweisung

Im folgenden Beispiel wird eine SELECT Anweisung erstellt und ausgeführt, die einen eingebetteten Parameter mit dem Namen @levelenthält.

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

B. Ausführen einer dynamisch erstellten Zeichenfolge

In folgenden Beispiel wird veranschaulicht, wie mithilfe von sp_executesql eine dynamisch erstellte Zeichenfolge ausgeführt wird. Mit der gespeicherten Prozedur im Beispiel werden Daten in mehrere Tabellen eingefügt, die zum Partitionieren der Jahresverkaufszahlen verwendet werden. Es gibt eine Tabelle für jeden Monat des Jahres mit dem folgenden 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)
);

Die gespeicherte Prozedur in diesem Beispiel erstellt eine INSERT-Anweisung dynamisch und führt sie aus, um neue Aufträge in die entsprechende Tabelle einzufügen. Im Beispiel wird das Bestelldatum verwendet, um den Namen der Tabelle zu erstellen, die die Daten enthalten soll. Anschließend wird dieser Name in eine INSERT-Anweisung integriert.

Hinweis

Dies ist ein einfaches Beispiel für sp_executesql. Das Beispiel enthält keine Fehlerüberprüfung und enthält keine Überprüfungen für Geschäftsregeln, z. B. die Gewährleistung, dass Bestellnummern nicht zwischen Tabellen dupliziert werden.

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

Die Verwendung sp_executesql in diesem Verfahren ist effizienter als EXECUTE die Verwendung der dynamisch erstellten Zeichenfolge, da sie die Verwendung von Parametermarkierungen ermöglicht. Parametermarkierungen machen es wahrscheinlicher, dass die Datenbank-Engine den generierten Abfrageplan wiederverwendet, wodurch zusätzliche Abfragekompilierungen vermieden werden können. Bei EXECUTEjeder Zeichenfolge ist jede INSERT Zeichenfolge eindeutig, da sich die Parameterwerte unterscheiden und am Ende der dynamisch generierten Zeichenfolge angefügt werden. Wenn die Abfrage ausgeführt wird, würde die Abfrage nicht auf eine Weise parametrisiert werden, die die Wiederverwendung des Plans fördert, und muss vor der Ausführung jeder INSERT Anweisung kompiliert werden, wodurch ein separater zwischengespeicherter Eintrag der Abfrage im Plancache hinzugefügt würde.

C. Verwenden des OUTPUT-Parameters

Im folgenden Beispiel wird ein OUTPUT Parameter verwendet, um das von der SELECT Anweisung im @SQLString Parameter generierte Resultset zu speichern. Anschließend werden zwei SELECT Anweisungen ausgeführt, die den Wert des OUTPUT Parameters verwenden.

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;

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

D: Ausführen einer SELECT-Anweisung

Im folgenden Beispiel wird eine SELECT Anweisung erstellt und ausgeführt, die einen eingebetteten Parameter mit dem Namen @levelenthält.

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

Zugehöriger Inhalt