Using sp_executesql
wykonać ciąg, zaleca się użycie sp_executesql przechowywane procedury zamiast instrukcja wykonać.Ponieważ ta procedura przechowywana obsługuje Podstawienie parametru sp_executesql jest bardziej uniwersalny niż wykonać; a ponieważ sp_executesql generuje planów wykonać, które mogą być wielokrotnie używane przez SQL Server, sp_executesql jest bardziej efektywne niż wykonać.
Samodzielne instancje
When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch.SQL Server compiles the Transact-SQL statement or statements in the string into an execution plan that is separate from the execution plan of the batch that contained the sp_executesql or the EXECUTE statement.Dla instancji samodzielne obowiązują następujące reguły:
The Transact-SQL statements in the sp_executesql or wykonać ciąg are not compiled into an execution plan until sp_executesql or the wykonać instrukcja are executed.Ciągi nie są analizowane lub sprawdzane pod kątem błędów, aż do ich wykonywania.Nazwy, do którego odwołuje się ciągi nie są rozpoznawane, aż do ich wykonywania.
The Transact-SQL statements in the executed ciąg do not have access to any variables declared in the partia that contains sp_executesql or the wykonać instrukcja.Wsadowy, który zawiera sp_executesql lub instrukcja wykonać nie ma dostęp do zmiennych lub lokalnego kursory zdefiniowane w ciąg wykonywany.
Jeżeli wykonywany ciąg zawiera instrukcję USE, że zmiany kontekstu bazy danych, zmiany kontekstu bazy danych obowiązuje tylko do sp_executesql lub instrukcja wykonać zakończył działanie.
Uruchomione następujące dwie partie ilustruje następujące punkty:
/*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
Podstawianie wartości parametru
sp_executesql wszystkie parametry, które są określone w obsługuje podstawiania wartości parametruTransact-SQL ciąg znaków, ale instrukcja wykonać nie obsługuje. Dlatego też Transact-SQL ciągi, które zostały wygenerowane przez sp_executesql są bardziej podobne niż te, które zostały wygenerowane przez instrukcja wykonać.The SQL Server optymalizator kwerendy will probably match the Transact-SQL statements from sp_executesql with execution plans from the previously executed statements, saving the overhead of compiling a new execution plan.
Przy użyciu instrukcja wykonać wszystkich wartości parametru musi być konwertowany na znak lub Unicode i dokonywane w ramach Transact-SQL ciąg znaków.
Jeśli w instrukcja jest wykonywana wielokrotnie, a całkowicie nowa Transact-SQL ciąg musi być wbudowane dla każdego wykonania, nawet wówczas, gdy są tylko różnice w wartościach podany dla parametrów. Generuje dodatkowe obciążenie w następujący sposób:
Zdolność SQL Server optymalizator kwerendy w celu dopasowania do nowej Transact-SQL ciąg składający się z istniejącego planu wykonania jest utrudniona przez stale zmieniających wartości parametru w tekście ciągu, szczególnie w zespolonych Transact-SQL instrukcje.
Cały ciąg musi być przebudowany dla każdego wykonania.
Wartości parametrów (innych niż znak lub wartości w standardzie Unicode) musi być rzutowane na znak lub w formacie Unicode dla każdego wykonania.
sp_executesql obsługuje ustawienie wartości parametru oddzielnie od Transact-SQL ciąg:
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 oferuje następujące dodatkowe korzyści:
Ponieważ tekstu Transact-SQL Instrukcja nie powoduje zmiany między wykonań, optymalizator kwerendy powinna być taka sama Transact-SQL Instrukcja w drugim wykonaniu z plan wykonania wygenerowany dla przy pierwszym wykonaniu. Dlatego też SQL Server nie ma do kompilowania druga instrukcja.
Ciąg Transact-SQL jest tworzony tylko raz.
Nondeterministic w programie SQL Server 2005 i później wersje są pewne Conversions daty/godzinyKonwersja na Unicode nie jest wymagane.
Uwaga
Nazwy obiektów w ciąg instrukcja, musi być w pełni kwalifikowana dla SQL Server Aby ponownie użyć planu wykonania.
Ponowne używanie wykonanie plany
We wcześniejszych wersjach SQL Server, jedynym sposobem, by mieć możliwość ponownego użycia planów wykonać jest zdefiniowanie Transact-SQL instrukcje w procedurze przechowywanej i aplikacji, wykonaj procedura przechowywana. Generuje dodatkowe obciążenie administracyjne dla aplikacji.Using sp_executesql can help reduce this overhead and still let SQL Server reuse execution plans.sp_executesql can be used instead of stored procedures when executing a Transact-SQL statement several times, when the only variation is in the parameter values supplied to the Transact-SQL statement.Ponieważ Transact-SQL instrukcje same pozostają stała i tylko zmiana wartości parametru, SQL Server optymalizator kwerendy jest prawdopodobne ponownie użyć generuje przy pierwszym wykonaniu plan wykonania.
W poniższym przykładzie kompiluje i wykonuje DBCC CHECKDB instrukcja dla każdej bazy danych na serwerze, z wyjątkiem dla czterech bazy danych systemu.
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
The SQL Server ODBC driver uses sp_executesql to implement SQLExecDirect when the Transact-SQL instrukcja that is being executed contains bound parameter markers. Ta rozszerza korzyści, dostarczone przez sp_executesql dla wszystkich aplikacji, korzystających z interfejsu API ODBC lub zdefiniowane przez ODBC, takich jak RDO.Istniejące aplikacje ODBC przenoszone do SQL Server automatycznie uzyskać zwiększenie wydajności bez konieczności ponownie zapisywane, tak być przechowywały. Jedynym wyjątkiem jest sp_executesql nie jest używany z parametrami danych na wykonanie.Aby uzyskać więcej informacji zobaczZa pomocą deklaracji Parameters.
The SQL Server Native klient ODBC dostawca also uses sp_executesql to implement the direct execution of statements with bound parameters.Aplikacje korzystające z OLE DB lub obiektów ADO uzyskać korzyści, dostarczone przez sp_executesql bez konieczności ponownie zapisywane, tak być przechowywały.
See Also