sp_executesql (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL Analytics-Endpunkt in Microsoft FabricWarehouse 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.

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 Beispieldatenbank, die Sie auf der Startseite von Microsoft SQL Server-Beispielen und Communityprojekten herunterladen können.

Argumente

[ @stmt = ] N'statement'

Eine Unicode-Zeichenfolge, die eine Transact-SQL-Anweisung oder einen Batch enthält. @stmt muss entweder eine Unicode-Konstante oder eine Unicode-Variable sein. Komplexere Unicode-Ausdrücke, z. B. das Verketten 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 ist beispielsweise gültig, aber die Zeichenkonstante N'sp_who''sp_who' ist 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';

Jeder parameter, der in @stmt enthalten ist, muss sowohl in der @params Parameterdefinitionsliste als auch in der Parameterwerteliste einen entsprechenden Eintrag aufweisen.

[ @params = ] N'@parameter_namedata_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, der weitere Parameterdefinitionen angibt. 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 = ] 'Wert1'

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 aufweist.

{ 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 die OUTPUT Schlüsselwort (keyword) verwendet, kann ein Cursorplatzhalter sein, es sei denn, die Prozedur ist eine CLR-Prozedur.

[ ... n ]

Ein Platzhalter für die Werte zusätzlicher Parameter. Werte können nur Konstanten oder Variablen sein. Werte können nicht komplexere Ausdrücke wie Funktionen oder Ausdrücke sein, die mithilfe von Operatoren erstellt wurden.

Rückgabecodewerte

0 (Erfolg) oder ungleich Null (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 außerhalb der Reihenfolge eingegeben werden, tritt eine Fehlermeldung auf.

sp_executesql hat das gleiche Verhalten wie EXECUTE bei Batches, dem Bereich der Namen und dem Datenbankkontext. Die Transact-SQL-Anweisung oder der sp_executesqlBatch 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.

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

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition 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.EmployeeAdventureWorks2022 Beispieldatenbank abgerufen und im Ausgabeparameter @max_titlezurückgegeben.

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @max_title 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.

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.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500);
DECLARE @OrderMonth 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 in diesem Verfahren ist effizienter als EXECUTE das Ausführen sp_executesql einer Zeichenfolge. Wenn sp_executesql sie verwendet wird, gibt es nur 12 Versionen der INSERT Zeichenfolge, die generiert werden, eine für jede monatliche Tabelle. Bei EXECUTEjeder Zeichenfolge ist jede INSERT Zeichenfolge eindeutig, da die Parameterwerte unterschiedlich sind. Obwohl beide Methoden dieselbe Anzahl von Batches generieren, macht die Ähnlichkeit der von sp_executesql ihnen INSERT generierten Zeichenfolgen wahrscheinlicher, dass der Abfrageoptimierer Ausführungspläne wiederverwendet.

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 NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @SalesOrderNumber NVARCHAR(25);
DECLARE @IntVariable 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

• EXECUTE (Transact-SQL)
• Gespeicherte Systemprozeduren (Transact-SQL)