Expand SP_ExecuteSQL Call
Introduction
Section titled “Introduction”The Expand SP_ExecuteSQL Callcommand expands all calls to the sp_executesql procedure in the current selection or document.
⚡ ** note: ** Not all sp_executresql calls can be expanded, but just the ones that have the @stmt and @params procedure parameters are declared and assigned in the scope of the batch and do not contain string concatenation.
Example
Section titled “Example”Input script:
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 AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level';SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @stmt = @SQLString, @params = @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;SELECT @max_title;Result after the sp_executesql procedure call is expanded:
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 AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level';SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
SELECT @max_title = max(JobTitle) FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @IntVariableSELECT @max_title;