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

    backup bigdata book database dates denali functions gotcha how to howto indexing mongodb nosql performance sql sql advent 2012 sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sqlcop ssis ssms ssrs t-sql tip

    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? »
    10
    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
    Instapaper

    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 SitingsTwitterFacebookLinkedInHomePageFlickrLTD RSS Feed
    10564 views
    Read More Posts by SQLDenis
    InstapaperVote on HN

    10 comments

    Comment from: Natas [Member] Email
    *****
    Natas 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
    ****-
    Impal3r 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 Nosonovsky [Member]
    *****
    Naomi Nosonovsky Excellent
    08/21/09 @ 10:59
    Comment from: NikX [Visitor]
    *****
    NikX Excellent step by step process..
    10/05/09 @ 05:51
    Comment from: Matthew [Visitor] Email
    Matthew 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
    Comment from: Andrew Eisenberg [Visitor]
    Andrew Eisenberg real power of sp_executesql lies not only in the aformentioned re-use
    of cached plans while changing param values passed, but the output
    param returned can be a variable of type XML, thus you get back a
    @var XML that can then be + create a function returning a table (reads contents of said
    @var and returns contents) magical ?!

    declare @Paged XML
    declare @spe varchar(max)
    set @Paged = (
    select Case_ID, Created_By, Date_Approved,
    from ( select cs.*
    from #CaseCollection cs,
    #Case c
    where cs.case_id = c.Case_ID
    ) x
    for XML RAW('PagedCases'))

    EXEC sp_executesql
    @spe
    , N'@Paged XML output'
    , @Paged=@Paged output

    select a.*, b.*
    from a
    , dbo.function (@Paged) b -- of course create the func mentioned above
    where a.case_id = a.case_id
    05/11/10 @ 17:35
    Comment from: Igor [Visitor]
    Igor Thank you very much for this article!!

    P.S.
    "How would you get a count of rows in a table? with EXEC you need to use a temp table and populate that" - when use EXEC it's very simple with @@ROWCOUNT =))
    02/08/12 @ 02:33
    Comment from: SQLDenis [Member] Email
    SQLDenis Igor,

    @@rowcount only works if you are actually returning all the rows from the table, so for a table with 10 million rows you would return all rows to get a count? Seems very expensive to me

    In this example @@rowcount is one

    select count(*) from sys.objects

    select @@rowcount

    so the only way to make it work is

    select object_id from sys.objects

    select @@rowcount

    But now you have an extra result set
    02/08/12 @ 02:41
    Comment from: Igor [Visitor]
    Igor SQLDenis, I was mistaken because my query is INSERT and therefore I can use @@ROWCOUNT.

    Sorry for my closed-mind comment :)
    02/08/12 @ 03:13
    Comment from: Santosh Mishra [Visitor] Email
    Santosh Mishra Hi,
    You really have explained the usage of parameterized dynamic query very well. But I have scenario where I am unanble to use this parameterized query. What I want is like I am passing few input parameters that can be null. If the input parameter's value is NULL, then this filter should not be applied. somethink like below:

    declare @a varchar(10), @b int
    set @a=null
    set @b=null

    declare @qry nvarchar(1000)
    set @qry=N'select * from dbo.Table1 where 1=1 '

    if @a is not null
    set @qry= @qry + N' and col1=''' + @a + ''''

    if @b is not null
    set @qry = @qry + N' and col2=' & cast(@b as varchar)

    exec sp_executesql @qry


    How can I use parameterized dynamic query in this case?
    08/20/12 @ 13:56

    Leave a comment


    Your email address will not be revealed on this site.

    To mislead the spambots.

    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