Verwenden von sp_executesql

Zum Ausführen einer Zeichenfolge sollte die gespeicherte Prozedur sp_executesql anstelle der EXECUTE-Anweisung verwendet werden. Die gespeicherte Prozedur sp_executesql ist aufgrund der Unterstützung der Parameterersetzung nicht nur flexibler als EXECUTE, sondern auch insofern effizienter, da sp_executesqlAusführungspläne generiert, die SQL Server 2005 mit größerer Wahrscheinlichkeit wiederverwendet.

Eigenständige Batches

Wenn eine Zeichenfolge von sp_executesql oder der EXECUTE-Anweisung ausgeführt wird, erfolgt die Ausführung als eigenständiger Batch. SQL Server kompiliert die Transact-SQL-Anweisungen in der Zeichenfolge in einen Ausführungsplan, der unabhängig vom Ausführungsplan des Batches ist, der sp_executesql oder die EXECUTE-Anweisung enthielt. Für eigenständige Batches gelten diese Regeln:

• Die Transact-SQL-Anweisungen in der sp_executesql- oder EXECUTE-Zeichenfolge werden erst dann in einen Ausführungsplan kompiliert, wenn sp_executesql oder die EXECUTE-Anweisung ausgeführt wird. Die Zeichenfolgen werden erst dann analysiert und auf Fehler überprüft, wenn sie ausgeführt werden. Die Namen, auf die in den Zeichenfolgen verwiesen wird, werden erst aufgelöst, wenn diese ausgeführt werden.
• Die Transact-SQL-Anweisungen in der ausgeführten Zeichenfolge haben keinen Zugriff auf die deklarierten Variablen in dem Batch, der sp_executesql oder die EXECUTE-Anweisung enthält. Der Batch, der sp_executesql oder die EXECUTE-Anweisung enthält, hat keinen Zugriff auf Variablen oder lokale Cursor, die in der ausgeführten Zeichenfolge definiert werden.
• Wenn die ausgeführte Zeichenfolge eine USE-Anweisung enthält, die den Datenbankkontext ändert, bleibt die Änderung des Datenbankkontextes nur so lange bestehen, bis sp_executesql oder die EXECUTE-Anweisung abgeschlossen ist.

Die Ausführung der folgenden beiden Batches veranschaulicht diese Punkte:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Ersetzen von Parameterwerten

sp_executesql unterstützt die Ersetzung von Parameterwerten für alle in der Transact-SQL-Zeichenfolge angegebenen Parameter. Dies gilt jedoch nicht für die EXECUTE-Anweisung. Daher sind die von sp_executesql generierten Transact-SQL-Zeichenfolgen einander ähnlicher als die von der EXECUTE-Anweisung generierten Zeichenfolgen. Der SQL Server-Abfrageoptimierer stimmt nach Möglichkeit die Transact-SQL-Anweisungen aus sp_executesql mit Ausführungsplänen von zuvor ausgeführten Anweisungen ab, sodass der Verwaltungsaufwand für die Kompilierung eines neuen Ausführungsplanes entfällt.

Bei der EXECUTE-Anweisung müssen alle Parameterwerte in Zeichen- oder Unicodedaten konvertiert und zu einem Bestandteil der Transact-SQL-Zeichenfolge gemacht werden.

Wenn die Anweisung wiederholt ausgeführt wird, muss für jede Ausführung eine vollständig neue Transact-SQL-Zeichenfolge erstellt werden, auch wenn die einzigen Unterschiede in den für die Parameter bereitgestellten Werten bestehen. Dies verursacht auf folgende Weise zusätzlichen Verwaltungsaufwand:

• Die Möglichkeit des SQL Server-Abfrageoptimierers zum Abstimmen der neuen Transact-SQL-Zeichenfolge mit einem vorhandenen Ausführungsplan wird durch die sich jedes Mal ändernden Parameterwerte im Text der Zeichenfolge beeinträchtigt, insbesondere bei komplexen Transact-SQL-Anweisungen.
• Die gesamte Zeichenfolge muss für jede Ausführung neu erstellt werden.
• Parameterwerte, die keine Zeichen- oder Unicode-Werte sind, müssen für jede Ausführung in ein Zeichen- oder Unicode-Format konvertiert werden.

sp_executesql unterstützt das Festlegen von Parameterwerten separat von der Transact-SQL-Zeichenfolge:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql bietet die folgenden zusätzlichen Vorteile:

• Da sich der eigentliche Text der Transact-SQL-Anweisung bei den verschiedenen Ausführungen nicht ändert, sollte der Abfrageoptimierer bei der zweiten Ausführung die Transact-SQL-Anweisung mit dem Ausführungsplan abgleichen, der für die erste Ausführung erstellt 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.

  Hinweis:
  Objektnamen in der Anweisungszeichenfolge müssen vollqualifiziert sein, damit SQL Server den Ausführungsplan wieder verwenden kann.

Wiederverwenden von Ausführungsplänen

In älteren Versionen von SQL Server bestand die einzige Möglichkeit zur Wiederverwendung von Ausführungsplänen darin, die Transact-SQL-Anweisungen als gespeicherte Prozedur zu definieren und die gespeicherte Prozedur von der Anwendung ausführen zu lassen. Dies verursacht zusätzlichen Verwaltungsaufwand für die Anwendungen. Das Verwenden von sp_executesql kann zur Reduzierung dieses Verwaltungsaufwands beitragen, wobei SQL Server weiterhin Ausführungspläne wiederverwenden kann. sp_executesql kann anstelle von gespeicherten Prozeduren verwendet werden, wenn eine Transact-SQL-Anweisung mehrmals ausgeführt wird und sich nur die Parameterwerte für die Transact-SQL-Anweisung ändern. Da die Transact-SQL-Anweisungen selbst unverändert bleiben und nur die Parameterwerte geändert werden, wird der SQL Server-Abfrageoptimierer wahrscheinlich den Ausführungsplan wieder verwenden, der für die erste Ausführung erstellt wird.

Dieses Beispiel erstellt eine DBCC CHECKDB-Anweisung für jede Datenbank auf einem Server und führt sie aus; hiervon ausgenommen sind die vier Systemdatenbanken:

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY'
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO

Der SQL Server-ODBC-Treiber verwendet sp_executesql zur Implementierung von SQLExecDirect, wenn die ausgeführte Transact-SQL-Anweisung gebundene Parametermarkierungen enthält. Dadurch stehen die Vorteile von sp_executesql für alle Anwendungen zur Verfügung, die ODBC oder über ODBC definierte APIs verwenden (z. B. RDO). Vorhandene, nach SQL Server 2005 portierte ODBC-Anwendungen können die Leistungsvorteile automatisch nutzen, ohne neu geschrieben zu werden. Die einzige Ausnahme besteht darin, dass sp_executesql nicht bei Data-at-Execution-Parametern (Daten-in-Ausführung) verwendet wird. Weitere Informationen finden Sie unter Using Statement Parameters.

Der SQL Native Client-ODBC-Anbieter verwendet sp_executesql außerdem dazu, die direkte Ausführung von Anweisungen mit gebundenen Parametern zu implementieren. Anwendungen, die OLE DB oder ADO verwenden, können die Vorteile von sp_executesql nutzen, ohne neu geschrieben werden zu müssen.

Siehe auch

Andere Ressourcen

SQL Injection
DECLARE @local\_variable (Transact-SQL)
SELECT (Transact-SQL)
sp_executesql (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005