Today I am going to talk about the stored proc sp_executesql and the benefits it has over EXEC @SQL. Microsoft actually recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. This is because of something called parameter substitution, which makes Sp_executesql a far more efficient method of executing dynamic SQL.

Because the actual text of the T-SQL in the sp_executesql string does not change between executions, the query optimizer will probably match the T-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement. This is not the case for EXEC @SQL.

One of the examples on TechNet is pretty good at demonstrating parameter returns and parameter substitution. It also uses AdventureWorks2012, which you can download here.

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 AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

A few things worth pointing out from the script above is that you can set the parameters (like in the example above) by declaring the @ParmDefinition and then setting it to include the parameters. Or, if there are not many parameters like the example above, just add them when you are executing sp_executesql. I’ve also moved the DECLARE @max_title to just before it is actually used.To me this looks a little neater and a little clearer to follow:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level';

DECLARE @max_title varchar(30);
EXECUTE sp_executesql @SQLString, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT, N'@level tinyint, @max_titleOUT varchar(30) OUTPUT'
SELECT @max_title;

By using the parameter OUTPUT to store the result set generated by the SELECT statement in the @SQLString parameter we can use the @max_title parameter in other queries.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level';

DECLARE @max_title varchar(30);
EXECUTE sp_executesql @SQLString, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT, N'@level tinyint, @max_titleOUT varchar(30) OUTPUT'
SELECT @max_title;

select max (JobTitle) FROM AdventureWorks2012.HumanResources.Employee
WHERE JobTitle = @max_title

select BusinessEntityID FROM AdventureWorks2012.HumanResources.Employee
WHERE JobTitle = @max_title

Although the order of the parameters is not important, you may cause this error if you try to add any further parameters in a form different from the @a_param = @b_param.

Must pass parameter number 4 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

To make it a little clearer, the example below has moved the parameter definition after we have set the parameter substitution. In this order the sp_executesql will not work and the error above will appear.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
FROM AdventureWorks2012.HumanResources.Employee
WHERE BusinessEntityID = @level';

DECLARE @max_title varchar(30);
EXECUTE sp_executesql @SQLString, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT, N'@level tinyint, @max_titleOUT varchar(30) OUTPUT'
SELECT @max_title;

When I first discovered sp_executesql and it’s benefits over EXEC @SQLString statement, I realised that it would be a big help to me when writing dynamic SQL. I hope that this has proved helpful for you too!