sp_executesql (Transact-SQL)
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 N
Prä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_executesql
Batches 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 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.Employee
AdventureWorks2022
Beispieldatenbank abgerufen und im Ausgabeparameter @max_title
zurü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 @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. 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 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 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 EXECUTE
jeder 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 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 @level
enthält.
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;