Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly

I was looking through some code recently and noticed all these sp_executesql calls which did not use parameters correctly.

A typical SQL statement would look like this

declare @Col2 smallint
declare @Col1 int

select @Col2 = 4,@Col1 = 5

declare @SQL nvarchar(1000)
select @SQL = 'select * from test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)



exec sp_executesql @SQL

What that code does is it builds a SQL statement and executes it. The problem is that when you do something like that the query plan will not be reused when you change the values of @Col2 and @Col1. When a new plan is generated everytime your values change you will bloat SQL Server’s procedure cache and less memory will be available for data.

Below is some code to demonstrate what I mean, I have tested this code on SQL Server 2008 only!!

First create this table

create table dbo.test (Col1 int primary key,
Col2 smallint not null,
SomeDate datetime default getdate(),
SomeValue char(10) default 'ABCDEFG')
GO

Insert a bunch of rows

insert dbo.test(Col1,Col2)
select number+ 1,number from master..spt_values
where type = 'P'
order by number

Now let’s see what we inserted

select * from dbo.test

(results abridged)

Col1 Col2 SomeDate SomeValue

1 0 2009-06-09 11:50:04.327 ABCDEFG

2 1 2009-06-09 11:50:04.327 ABCDEFG

3 2 2009-06-09 11:50:04.327 ABCDEFG

4 3 2009-06-09 11:50:04.327 ABCDEFG

5 4 2009-06-09 11:50:04.327 ABCDEFG

6 5 2009-06-09 11:50:04.327 ABCDEFG

7 6 2009-06-09 11:50:04.327 ABCDEFG

…..

…..

…..

2047 2046 2009-06-09 11:50:04.327 ABCDEFG

2048 2047 2009-06-09 11:50:04.327 ABCDEFG

First let’s clear our procedure cache

dbcc freeproccache

run these 2 queries 5 times

select * from dbo.test
where Col2 = 3
and Col1 = 4
go

select * from dbo.test
where Col2 = 4
and Col1 = 5
go

Now run the following query to see how many plans we have.

select q.text,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and q.text  like '%dbo.test%'
and q.text  not like '%sys.dm_exec_cached_plans %'

As you can see we have 2 plans and each was used 5 times. So for each change in the value a new plan gets generated

Let’s clear the cache again

dbcc freeproccache

Using dynamic SQL with changing parameters also creates a new plan every time you change the values of the parameters.

Run the following block of code 5 times

declare @Col2 smallint
declare @Col1 int

select @Col2 = 11,@Col1 = 12

declare @SQL varchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)


exec (@SQL)

go

declare @Col2 smallint
declare @Col1 int

select @Col2 = 12,@Col1 = 13

declare @SQL varchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)


exec (@SQL)
go

Now let’s see how many plans we have

select q.text,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and q.text  like '%dbo.test%'
and q.text  not like '%sys.dm_exec_cached_plans %'

As you can see we have 2 plans with a count of 5 for each.

Now let’s convert that query to use sp_executesql instead of exec

Run the query below

declare @Col2 smallint
declare @Col1 int

select @Col2 = 3,@Col1 = 4

declare @SQL varchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)



exec sp_executesql @SQL

And you get the following message

**Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.**

This is because sp_executesql expects nvarchar and not varchar

Below is the correct query(but it is not correctly parameterized). First clear the cache again

dbcc freeproccache

Now run the following queries 5 times each

declare @Col2 smallint
declare @Col1 int

select @Col2 = 23,@Col1 = 24

declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)


exec sp_executesql @SQL
Go


declare @Col2 smallint
declare @Col1 int

select @Col2 = 22,@Col1 = 23

declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = ' + convert(varchar(10),@Col2)+ '
and Col1 = ' + convert(varchar(10),@Col1)



exec sp_executesql @SQL
GO

Now check again for the plans

select q.text,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and q.text  like '%dbo.test%'
and q.text  not like '%sys.dm_exec_cached_plans %'

As you can see we have 2 plans with a count of 5 for each. This is because we didn’t use sp_executesql correctly and the engine couldn’t reuse the plan. Here is what Books On Line has to say

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the Microsoft® SQL Server™ query optimizer is likely to reuse the execution plan it generates for the first execution.

Below is the query which is correctly parameterized. As you can see we have variables inside the string and at execution time we pass values by means of other variables to it.

First clear the cache again

dbcc freeproccache

Now run the following queries 5 times each

declare @Col2 smallint, @Col1 int
select @Col2 = 3,@Col1 = 4


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = @InnerCol2 and Col1 = @InnerCol1' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'



exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerCol2 	= @Col2,
			@InnerCol1 	= @Col1
go


declare @Col2 smallint, @Col1 int
select @Col2 = 3,@Col1 = 4


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = @InnerCol2 and Col1 = @InnerCol1' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'



exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerCol2 	= @Col2,
			@InnerCol1 	= @Col1

go

Check the plans again

select q.text,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and q.text  like '%dbo.test%'
and q.text  not like '%sys.dm_exec_cached_plans %'

And you will see that we have only one plan with a count of 10

Instead of running the query like we did before we can also do the following. We only have to declare everything once and then we just need to change the values of the parameters before executing

First clear the cache yet again

dbcc freeproccache

Here is the rewritten query, execute it 5 times

declare @Col2 smallint, @Col1 int
select @Col2 = 3,@Col1 = 4


declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.test
where Col2 = @InnerCol2 and Col1 = @InnerCol1' 

declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'



exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerCol2 	= @Col2,
			@InnerCol1 	= @Col1

--change param values and run the same query
select @Col2 = 2,@Col1 = 3
exec sp_executesql 	@SQL,@ParmDefinition,
			@InnerCol2 	= @Col2,
			@InnerCol1 	= @Col1

go 

And we will check the plans yet again

select q.text,cp.usecounts,cp.objtype,p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and q.text  like '%dbo.test%'
and q.text  not like '%sys.dm_exec_cached_plans %'

As you can see we still have a count of 10 and only one plan.

As you can see sp_executesql can be beneficial for performance when used correctly. Using sp_executesql will also give you some additional features you can’t do with EXEC.

How would you get a count of rows in a table? with EXEC you need to use a temp table and populate that, with sp_executesql you can use an output variable

Take a look at the following queries

Here is the EXEC version

--EXEC (SQL)
DECLARE @TableName VARCHAR(100),
@TableCount INT,
@SQL NVARCHAR(100)
 
 
CREATE TABLE #temp (Totalcount INT)
SELECT @TableName = 'test'
SELECT @SQL = 'Insert into #temp Select Count(*) from ' + @TableName
 
EXEC( @SQL)
 
SELECT @TableCount = Totalcount FROM #temp
 
SELECT @TableCount as TheCount
 
DROP TABLE #temp
GO

Here is the sp_executesql version

--sp_executesql
DECLARE @TableName VARCHAR(100),
@TableCount INT,
@SQL NVARCHAR(100)
 
SELECT @TableName = 'Test'
SELECT @SQL = N'SELECT @InnerTableCount = COUNT(*) FROM ' + @TableName
 
EXEC SP_EXECUTESQL @SQL, N'@InnerTableCount INT OUTPUT', @TableCount OUTPUT
 
SELECT @TableCount
GO

There are more differences between EXEC and sp_executesql, one of the more important one is that sp_executesql can protect you from SQL Injection. I encourage you to read The curse and blessings of dynamic SQL to learn more stuff

I have written a follow up to this post that explains how to avoid conversions. Here is the link: Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum