我有一堆存盤程序，我正在尋找優化，想知道從將變數注入 WHERE 條件更改為將它們注入變數宣告是否會有所幫助
當前陳述句看起來像這樣，其中 id 被傳遞到存盤程序，然后注入到 EXEC
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
SELECT * FROM Employees WHERE Id = ' @EmployeeId '
SELECT * FROM Customers WHERE Id = ' @CustomerId '
')
將注入的值更改為變數是否有任何好處？
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
DECLARE @EmployeeId int = ' @EmployeeId '
DECLARE @CustomerId int = ' @CustomerId '
SELECT * FROM Employees WHERE Id = @EmployeeId
SELECT * FROM Customers WHERE Id = @CustomerId
')
uj5u.com熱心網友回復：
我建議執行引數化查詢而不是注入變數。這將促進執行計劃的重用并帶來其他好處。
DECLARE @EmployeeId int = 123;
DECLARE @CustomerId int = 456;
EXEC sp_executesql
N'SELECT * FROM Employees WHERE Id = @EmployeeId;SELECT * FROM Customers WHERE Id = @CustomerId'
,N'@EmployeeId int, @CustomerId int'
,@EmployeeId = @EmployeeId
,@CustomerId = @CustomerId;
uj5u.com熱心網友回復：
用第一種方式做
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
SELECT * FROM Employees WHERE Id = ' @EmployeeId '
SELECT * FROM Customers WHERE Id = ' @CustomerId '
')
意味著可以為每次運行生成一個新的查詢計劃。雖然自動引數化在這里確實可以作業，但它可能不會，因此您每次都要承受重新編譯的成本。
同時第二個版本，
DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456
EXEC('
DECLARE @EmployeeId int = ' @EmployeeId '
DECLARE @CustomerId int = ' @CustomerId '
SELECT * FROM Employees WHERE Id = @EmployeeId
SELECT * FROM Customers WHERE Id = @CustomerId
')
并沒有真正的幫助，因為整批每次都在變化，所以你仍然會得到重新編譯。它還將強制關閉自動引數化，并且您使用區域變數的事實意味著引數嗅探也被禁用。
正如@DanGuzman 所提到的，你可以使用sp_executesql
DECLARE @EmployeeId int = 123;
DECLARE @CustomerId int = 456;
EXEC sp_executesql N'
SELECT * FROM Employees WHERE Id = @EmployeeId;
SELECT * FROM Customers WHERE Id = @CustomerId
'
,N'@EmployeeId int, @CustomerId int'
,@EmployeeId = @EmployeeId
,@CustomerId = @CustomerId;
這將為您提供帶有引數嗅探功能的快取計劃。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/353653.html
標籤：sql sql-server 查询语句
上一篇：為什么我的資料讀取器只回傳一行？