Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

book business intelligence database dates dmv excel functions gemini gotcha hotfix how to howto indexing interview linq madison performance performance tuning programming sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 t-sql tip trick xml

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« Avoid Conversions In Execution Plans By Using sp_executesql Instead of ExecWhy does my variable always have NULL in it? »
5
comments

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

by SQLDenis on Jun 09, 2009 in categories Data Modelling & Design
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

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

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT
  2. DECLARE @Col1 INT
  3.  
  4. SELECT @Col2 = 4,@Col1 = 5
  5.  
  6. DECLARE @SQL NVARCHAR(1000)
  7. SELECT @SQL = 'select * from test
  8. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  9. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  10.  
  11.  
  12.  
  13. 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

tsql
Line number Off | Hide | Select all
  1. CREATE TABLE dbo.test (Col1 INT PRIMARY KEY,
  2. Col2 SMALLINT not null,
  3. SomeDate DATETIME DEFAULT GETDATE(),
  4. SomeValue CHAR(10) DEFAULT 'ABCDEFG')
  5. GO

Insert a bunch of rows

tsql
Line number Off | Hide | Select all
  1. INSERT dbo.test(Col1,Col2)
  2. SELECT number+ 1,number FROM master..spt_values
  3. WHERE type = 'P'
  4. ORDER BY number

Now let's see what we inserted

tsql
Line number Off | Hide | Select all
  1. 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

tsql
Line number Off | Hide | Select all
  1. DBCC freeproccache

run these 2 queries 5 times

tsql
Line number Off | Hide | Select all
  1. SELECT * FROM dbo.test
  2. WHERE Col2 = 3
  3. and Col1 = 4
  4. go
  5.  
  6. SELECT * FROM dbo.test
  7. WHERE Col2 = 4
  8. and Col1 = 5
  9. go

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

tsql
Line number Off | Hide | Select all
  1. SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
  2. q.*,
  3. cp.plan_handle
  4. FROM
  5. sys.dm_exec_cached_plans cp
  6. cross apply sys.dm_exec_query_plan(cp.plan_handle) p
  7. cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
  8. WHERE
  9. cp.cacheobjtype = 'Compiled Plan' and q.TEXT  like '%dbo.test%'
  10. 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

tsql
Line number Off | Hide | Select all
  1. 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

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT
  2. DECLARE @Col1 INT
  3.  
  4. SELECT @Col2 = 11,@Col1 = 12
  5.  
  6. DECLARE @SQL VARCHAR(1000)
  7. SELECT @SQL = 'select * from dbo.test
  8. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  9. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  10.  
  11.  
  12. EXEC (@SQL)
  13.  
  14. go
  15.  
  16. DECLARE @Col2 SMALLINT
  17. DECLARE @Col1 INT
  18.  
  19. SELECT @Col2 = 12,@Col1 = 13
  20.  
  21. DECLARE @SQL VARCHAR(1000)
  22. SELECT @SQL = 'select * from dbo.test
  23. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  24. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  25.  
  26.  
  27. EXEC (@SQL)
  28. go

Now let's see how many plans we have

tsql
Line number Off | Hide | Select all
  1. SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
  2. q.*,
  3. cp.plan_handle
  4. FROM
  5. sys.dm_exec_cached_plans cp
  6. cross apply sys.dm_exec_query_plan(cp.plan_handle) p
  7. cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
  8. WHERE
  9. cp.cacheobjtype = 'Compiled Plan' and q.TEXT  like '%dbo.test%'
  10. 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

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT
  2. DECLARE @Col1 INT
  3.  
  4. SELECT @Col2 = 3,@Col1 = 4
  5.  
  6. DECLARE @SQL VARCHAR(1000)
  7. SELECT @SQL = 'select * from dbo.test
  8. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  9. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  10.  
  11.  
  12.  
  13. 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

tsql
Line number Off | Hide | Select all
  1. DBCC freeproccache

Now run the following queries 5 times each

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT
  2. DECLARE @Col1 INT
  3.  
  4. SELECT @Col2 = 23,@Col1 = 24
  5.  
  6. DECLARE @SQL NVARCHAR(1000)
  7. SELECT @SQL = 'select * from dbo.test
  8. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  9. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  10.  
  11.  
  12. EXEC SP_EXECUTESQL @SQL
  13. Go
  14.  
  15.  
  16. DECLARE @Col2 SMALLINT
  17. DECLARE @Col1 INT
  18.  
  19. SELECT @Col2 = 22,@Col1 = 23
  20.  
  21. DECLARE @SQL NVARCHAR(1000)
  22. SELECT @SQL = 'select * from dbo.test
  23. where Col2 = ' + CONVERT(VARCHAR(10),@Col2)+ '
  24. and Col1 = ' + CONVERT(VARCHAR(10),@Col1)
  25.  
  26.  
  27.  
  28. EXEC SP_EXECUTESQL @SQL
  29. GO

Now check again for the plans

tsql
Line number Off | Hide | Select all
  1. SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
  2. q.*,
  3. cp.plan_handle
  4. FROM
  5. sys.dm_exec_cached_plans cp
  6. cross apply sys.dm_exec_query_plan(cp.plan_handle) p
  7. cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
  8. WHERE
  9. cp.cacheobjtype = 'Compiled Plan' and q.TEXT  like '%dbo.test%'
  10. 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

tsql
Line number Off | Hide | Select all
  1. DBCC freeproccache

Now run the following queries 5 times each

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT, @Col1 INT
  2. SELECT @Col2 = 3,@Col1 = 4
  3.  
  4.  
  5. DECLARE @SQL NVARCHAR(1000)
  6. SELECT @SQL = 'select * from dbo.test
  7. where Col2 = @InnerCol2 and Col1 = @InnerCol1'
  8.  
  9. DECLARE @ParmDefinition NVARCHAR(500)
  10. SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'
  11.  
  12.  
  13.  
  14. EXEC SP_EXECUTESQL  @SQL,@ParmDefinition,
  15.             @InnerCol2  = @Col2,
  16.             @InnerCol1  = @Col1
  17. go
  18.  
  19.  
  20. DECLARE @Col2 SMALLINT, @Col1 INT
  21. SELECT @Col2 = 3,@Col1 = 4
  22.  
  23.  
  24. DECLARE @SQL NVARCHAR(1000)
  25. SELECT @SQL = 'select * from dbo.test
  26. where Col2 = @InnerCol2 and Col1 = @InnerCol1'
  27.  
  28. DECLARE @ParmDefinition NVARCHAR(500)
  29. SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'
  30.  
  31.  
  32.  
  33. EXEC SP_EXECUTESQL  @SQL,@ParmDefinition,
  34.             @InnerCol2  = @Col2,
  35.             @InnerCol1  = @Col1
  36.  
  37. go

Check the plans again

tsql
Line number Off | Hide | Select all
  1. SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
  2. q.*,
  3. cp.plan_handle
  4. FROM
  5. sys.dm_exec_cached_plans cp
  6. cross apply sys.dm_exec_query_plan(cp.plan_handle) p
  7. cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
  8. WHERE
  9. cp.cacheobjtype = 'Compiled Plan' and q.TEXT  like '%dbo.test%'
  10. 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

tsql
Line number Off | Hide | Select all
  1. DBCC freeproccache

Here is the rewritten query, execute it 5 times

tsql
Line number Off | Hide | Select all
  1. DECLARE @Col2 SMALLINT, @Col1 INT
  2. SELECT @Col2 = 3,@Col1 = 4
  3.  
  4.  
  5. DECLARE @SQL NVARCHAR(1000)
  6. SELECT @SQL = 'select * from dbo.test
  7. where Col2 = @InnerCol2 and Col1 = @InnerCol1'
  8.  
  9. DECLARE @ParmDefinition NVARCHAR(500)
  10. SET @ParmDefinition = N'@InnerCol2 smallint ,@InnerCol1 int'
  11.  
  12.  
  13.  
  14. EXEC SP_EXECUTESQL  @SQL,@ParmDefinition,
  15.             @InnerCol2  = @Col2,
  16.             @InnerCol1  = @Col1
  17.  
  18. --change param values and run the same query
  19. SELECT @Col2 = 2,@Col1 = 3
  20. EXEC SP_EXECUTESQL  @SQL,@ParmDefinition,
  21.             @InnerCol2  = @Col2,
  22.             @InnerCol1  = @Col1
  23.  
  24. go

And we will check the plans yet again

tsql
Line number Off | Hide | Select all
  1. SELECT q.TEXT,cp.usecounts,cp.objtype,p.*,
  2. q.*,
  3. cp.plan_handle
  4. FROM
  5. sys.dm_exec_cached_plans cp
  6. cross apply sys.dm_exec_query_plan(cp.plan_handle) p
  7. cross apply sys.dm_exec_sql_text(cp.plan_handle) AS q
  8. WHERE
  9. cp.cacheobjtype = 'Compiled Plan' and q.TEXT  like '%dbo.test%'
  10. 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

tsql
Line number Off | Hide | Select all
  1. --EXEC (SQL)
  2. DECLARE @TableName VARCHAR(100),
  3. @TableCount INT,
  4. @SQL NVARCHAR(100)
  5.  
  6.  
  7. CREATE TABLE #temp (Totalcount INT)
  8. SELECT @TableName = 'test'
  9. SELECT @SQL = 'Insert into #temp Select Count(*) from ' + @TableName
  10.  
  11. EXEC( @SQL)
  12.  
  13. SELECT @TableCount = Totalcount FROM #temp
  14.  
  15. SELECT @TableCount AS TheCount
  16.  
  17. DROP TABLE #temp
  18. GO

Here is the sp_executesql version

tsql
Line number Off | Hide | Select all
  1. --sp_executesql
  2. DECLARE @TableName VARCHAR(100),
  3. @TableCount INT,
  4. @SQL NVARCHAR(100)
  5.  
  6. SELECT @TableName = 'Test'
  7. SELECT @SQL = N'SELECT @InnerTableCount = COUNT(*) FROM ' + @TableName
  8.  
  9. EXEC SP_EXECUTESQL @SQL, N'@InnerTableCount INT OUTPUT', @TableCount OUTPUT
  10.  
  11. SELECT @TableCount
  12. 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

About the Author

User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Social SitingsTwitterFacebookLinkedInHomePageLTD RSS Feed
3339 views
More Posts by SQLDenis
dynamic sql, exec, sp_executesql, sql
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

5 comments

Comment from: Natas [Member] Email
*****
Wow, I didn't know that. Will have to revisit some procs that run dynamic SQL

thanks
06/10/09 @ 06:36
Comment from: Impal3r [Member] Email
****-
This is very interesting, I never knew that sp_executesql could use output parameters, this will make my life soooo much easier
06/10/09 @ 07:35
Comment from: Naomi [Member] Email
*****
Excellent
08/21/09 @ 10:59
Comment from: NikX [Visitor]
*****
Excellent step by step process..
10/05/09 @ 05:51
Comment from: Matthew [Visitor] Email
Excellent post. I was not aware of these subtle differences. The MSDN article on this topic could do with an update by you!
11/22/09 @ 20:40

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS