Skip to content

Expand SP_ExecuteSQL Call

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.

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 = @IntVariable
SELECT @max_title;

Refactoring