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 bzw. der mehrfach wiederverwendet werden kann oder dynamisch erstellt wurde. Die Transact-SQL-Anweisung oder der -Batch können eingebettete Parameter enthalten.

Wichtig

Durch zur Laufzeit kompilierte Transact-SQL-Anweisungen können Anwendungen schädlichen Angriffen ausgesetzt sein.

Transact-SQL-Syntaxkonventionen

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

Argumente

[ @stmt= ] Anweisung
Ist eine Unicode-Zeichenfolge mit einer Transact-SQL-Anweisung oder einem Transact-SQL-Batch. @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. Wenn eine Unicode-Konstante angegeben wird, muss ihr ein N vorangestellt werden. Die Unicode-Konstante N'sp_who' ist z. B. 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.

Hinweis

@stmt kann Parameter enthalten, die dasselbe Format wie ein Variablenname aufweisen, z. B.: 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'@parameternamedatentyp [ ,... n ] '
Ist eine einzige Zeichenfolge, die die Definitionen aller Parameter enthält, die in @stmt eingebettet wurden. 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 zusätzliche 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= ] 'wert1'
Der 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. Die Parameter text, ntext und image können nur dann als OUTPUT-Parameter verwendet werden, wenn es sich bei der Prozedur nicht um eine CLR-Prozedur (Common Language Runtime) handelt. Ein Ausgabeparameter, der das Schlüsselwort OUTPUT verwendet, kann ein Cursorplatzhalter sein, es sei denn, bei der Prozedur handelt es sich um eine CLR-Prozedur (Common Language Runtime).

n
Ein Platzhalter für die Werte zusätzlicher 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)

Resultsets

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

Bemerkungen

sp_executesql-Parameter müssen in der Reihenfolge eingegeben werden, die früher in diesem Thema im Abschnitt „Syntax“ beschrieben wird. Wenn die Parameter nicht in der vorgegebenen Reihenfolge eingegeben werden, wird eine Fehlermeldung ausgegeben.

sp_executesql verhält sich hinsichtlich Batches, Namensbereichen und Datenbankkontext wie EXECUTE. Die Transact-SQL-Anweisung oder ein Transact-SQL-Batch im @stmt-Parameter „sp_executesql“ wird erst kompiliert, wenn die sp_executesql-Anweisung ausgeführt wird. Der Inhalt von @stmt wird dann kompiliert und als Ausführungsplan separat von dem Ausführungsplan des Batches ausgeführt, in dem sp_executesql aufgerufen wurde. Der sp_executesql-Batch kann nicht auf Variablen verweisen, die in dem Batch deklariert werden, der sp_executesql aufruft. Lokale Cursor oder Variablen im sp_executesql-Batch sind für den Batch, der sp_executesql aufruft, nicht sichtbar. Änderungen am Datenbankkontext sind nur bis zum Ende der sp_executesql-Anweisung vorhanden.

sp_executesql kann anstelle gespeicherter Prozeduren verwendet werden, um eine Transact-SQL-Anweisung viele Male auszuführen, wenn sich nur die Parameterwerte in der Anweisung ändern. 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.

Hinweis

Verwenden Sie zur Verbesserung der Leistung vollqualifizierte Objektnamen in der Anweisungszeichenfolge.

sp_executesql unterstützt das von der Transact-SQL-Zeichenfolge getrennte Festlegen von Parameterwerten, wie dem folgenden Beispiel zu entnehmen ist.

DECLARE @IntVariable INT;  
DECLARE @SQLString NVARCHAR(500);  
DECLARE @ParmDefinition NVARCHAR(500);  
  
/* Build the SQL string one time.*/  
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;  

Die Verwendung von Ausgabeparametern mit sp_executesql ist ebenfalls möglich. 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;  

Die Möglichkeit, Parameter in sp_executesql zu ersetzen, bietet die folgenden Vorteile gegenüber der Verwendung der EXECUTE-Anweisung, um eine Zeichenfolge auszuführen:

  • Da sich der eigentliche Text der Transact-SQL-Anweisung in der sp_executesql-Zeichenfolge für die verschiedenen Ausführungen nicht ändert, findet der Abfrageoptimierer für die Transact-SQL-Anweisung bei der zweiten Ausführung wahrscheinlich eine Übereinstimmung mit dem Ausführungsplan, der für die erste Ausführung generiert wurde. Deshalb muss SQL Server die zweite Anweisung nicht kompilieren.

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

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

Berechtigungen

Erfordert die Mitgliedschaft in der public-Rolle.

Beispiele

A. Ausführen einer einfachen SELECT-Anweisung

In diesem Beispiel wird eine einfache SELECT-Anweisung erstellt und ausgeführt, die den eingebetteten Parameter @level enthä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. Für jeden Monat des Jahres ist eine Tabelle mit dem folgenden Format vorhanden:

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 Fehlerprüfung und keine Überprüfung etwaiger Geschäftsregeln, wie z. B. um sicherzustellen, dass Bestellnummern in den Tabellen nicht doppelt vorhanden sind.

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  

Das Verwenden von sp_executesql in dieser Prozedur ist effizienter als das Verwenden von EXECUTE zum Ausführen einer Zeichenfolge. Beim Verwenden von sp_executesql werden nur 12 Versionen der INSERT-Zeichenfolge generiert, nämlich eine Version für jede Monatstabelle. Mit EXECUTE ist jede INSERT-Zeichenfolge eindeutig, weil die Parameterwerte unterschiedlich sind. Obwohl beide Methoden die gleiche Anzahl von Batches generieren, ist es wegen der Ähnlichkeit der von sp_executesql generierten INSERT-Zeichenfolgen wahrscheinlicher, dass der Abfrageoptimierer die 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 einfachen SELECT-Anweisung

In diesem Beispiel wird eine einfache SELECT-Anweisung erstellt und ausgeführt, die den eingebetteten Parameter @level enthält.

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

Weitere Informationen

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