A Quick Tour of sp_ExecuteSQL

  • Fatherjack

    SSCommitted

    Points: 1575

    Comments posted to this topic are about the item A Quick Tour of sp_ExecuteSQL

  • Daniel Brink

    SSC Veteran

    Points: 278

    Nice article. I'm still working on writing my first technical article. It's a bit of a daunting task, but I'm getting there.

    Regarding sp_ExecuteSQL: Dynamic SQL is one of those things I wish developers could un-learn as they often using it as an easy fix for bad design.

    I do have a quick question - what happens with the execution plan?

    - would the execution plan be regenerated on each run of the main procedure?

    - does/can it ever reuse execution plans on the dynamic SQL sections?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182457

    Thank you Jonathan for this article.

    Just a quick note on the last query, it will fail if a table is not in the default user schema.

    This will work for all schemas;

    DECLARE @TSQLDefinition NVARCHAR(500)

    DECLARE @TableNameVar NVARCHAR(100)

    DECLARE @SCHEMA_ID INT

    DECLARE @TableList TABLE

    (

    TableName NVARCHAR(100)

    ,schema_id INT

    )

    /* collect table names into a table variable */

    INSERT @TableList

    ( TableName,schema_id)

    ( SELECT Name ,schema_id

    FROM sys.tables

    WHERE type_desc = N'USER_TABLE')

    WHILE EXISTS ( SELECT *

    FROM @TableList )

    BEGIN

    /* build the TSQL statement we want executed for one table */

    SELECT TOP 1

    @TableNameVar = tl.TableName

    ,@SCHEMA_ID = tl.schema_id

    FROM @TableList AS tl

    SET @TSQLDefinition = N'SELECT COUNT(*) AS '

    + QUOTENAME(@TableNameVar) + N' FROM '

    + QUOTENAME(SCHEMA_NAME(@SCHEMA_ID)) + NCHAR(46)

    + QUOTENAME(@TableNameVar);

    /* execute the TSQL */

    EXEC sys.sp_executesql @TSQLDefinition;

    /* remove the tablename from the list of tables*/

    DELETE FROM @TableList

    WHERE TableName = @TableNameVar

    END

    GO

  • davoscollective

    SSCertifiable

    Points: 6355

    Daniel Brink (2/24/2014)


    Nice article. I'm still working on writing my first technical article. It's a bit of a daunting task, but I'm getting there.

    Regarding sp_ExecuteSQL: Dynamic SQL is one of those things I wish developers could un-learn as they often using it as an easy fix for bad design.

    I do have a quick question - what happens with the execution plan?

    - would the execution plan be regenerated on each run of the main procedure?

    - does/can it ever reuse execution plans on the dynamic SQL sections?

    There's a great article on that by Erland Sommarskog on exactly that, cache plan reuse and dynamic queries.

    Parameterized queries have reusable plans, sometimes that's a bad thing when different parameter values would benefit from different plans.

    http://sommarskog.se/dynamic_sql.html#queryplans

  • marcelo.m.moraes

    Valued Member

    Points: 66

    If I'm not wrong, the way sp_executesql is being used in this article is not the right one when we think about a better plan cache use.

    Something like the following sample would be most appropriate.

    EXEC SP_EXECUTESQL N'SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderID = @p', N'@p INT', 102480

  • jpk 91966

    SSC Enthusiast

    Points: 170

    Great Article, but I did notice something weird in my testing.

    I started with the first example which was a simple select * from a table and that worked without a hitch. I then tried the second example by adding a where condition. Again I had no problem so I went on to the 3rd example of adding multiple where conditions. Now the problems started.

    I keep getting:

    Msg 4145, Level 15, State 1, Line 1

    An expression of non-boolean type specified in a context where a condition is expected, near 'field 2 name'.

    I tried multiple fields of different types in different orders and kept getting the error.

    The only real difference between my tests and the examples was that I fully qualified my table. So just for the heck of it I removed the database and schema thinking there was no way it could be that but was willing to try anything and sure enough it started to work.

    Thanks,

    John

  • BXRWXR

    SSC Rookie

    Points: 45

    How is this different from plain old EXEC(@SomeSQLStatement)?

    BR

  • Tom Bakerman

    Hall of Fame

    Points: 3367

    BXRWXR (2/24/2014)


    How is this different from plain old EXEC(@SomeSQLStatement)?

    BR

    One big difference is that with sp_ExecuteSQL you can define parameters external to the @SomeSQLStatement variable, thereby reusing the dynamic sql with different parameter values.

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Tom Bakerman (2/24/2014)


    BXRWXR (2/24/2014)


    How is this different from plain old EXEC(@SomeSQLStatement)?

    BR

    One big difference is that with sp_ExecuteSQL you can define parameters external to the @SomeSQLStatement variable, thereby reusing the dynamic sql with different parameter values.

    The big significance of passing parameters is that using EXEC with an external interface that builds @SomeSQLStatement creates a SQL injection vulnerability. Properly coded parameterized dynamic SQL with sp_executeSQL avoids this.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Scott Coleman

    One Orange Chip

    Points: 27442

    sp_executesql does promote execution plan reuse. You can also declare OUTPUT parameters.

  • BXRWXR

    SSC Rookie

    Points: 45

    Cool. Thanks for the clarification.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply