Click or drag to resize
Expand SP_ExecuteSQL Call

The Expand SP_ExecuteSQL Call command expands all calls to the sp_executesql procedure in current selection or document.

Note 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 contatenation.

This topic contains the following sections:

Example

Input script:

 1DECLARE @IntVariable int;
 2DECLARE @SQLString nvarchar(500);
 3DECLARE @ParmDefinition nvarchar(500);
 4DECLARE @max_title varchar(30);
 5
 6SET @IntVariable = 197;
 7SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
 8   FROM AdventureWorks2008R2.HumanResources.Employee
 9   WHERE BusinessEntityID = @level';
10SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
11
12EXECUTE sp_executesql @stmt = @SQLString, @params = @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
13SELECT @max_title;

Result after the sp_executesql procedure call is expanded:

 1DECLARE @IntVariable int;
 2DECLARE @SQLString nvarchar(500);
 3DECLARE @ParmDefinition nvarchar(500);
 4DECLARE @max_title varchar(30);
 5
 6SET @IntVariable = 197;
 7SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
 8   FROM AdventureWorks2008R2.HumanResources.Employee
 9   WHERE BusinessEntityID = @level';
10SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';
11
12SELECT @max_title = max(JobTitle) 
13   FROM AdventureWorks2008R2.HumanResources.Employee
14   WHERE BusinessEntityID = @IntVariable
15SELECT @max_title;
See Also

Other Resources