我有一堆存储过程需要优化，想知道从将变量注入(inject) WHERE 条件更改为将它们注入(inject)变量声明是否会有帮助

当前语句看起来像这样，其中 id 被传递到存储过程中，然后注入(inject)到 EXEC 中

DECLARE @EmployeeId int = 123
DECLARE @CustomerId int = 456

EXEC('
SELECT * FROM Employees WHERE Id = ' + @EmployeeId + '

SELECT * FROM Customers WHERE Id = ' + @CustomerId + '
')

更改为将注入(inject)值完成到变量中的这种方式会有任何好处吗？

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
')

最佳答案

我建议执行参数化查询而不是注入(inject)变量。这将促进执行计划的重用，还有其他好处。

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://stackoverflow.com/questions/69889420/