Post reply

Executing dynamic SQL

  • January 17, 2013 at 7:09 am

    #272847

    I've been trying to find out why "Exec sp_executesql @sql" is supposed to be a better practice than "Exec (@sql)". I ran a few tests in SSMS and compared estimated and actual execution plans but could not determine any difference.

    Does anyone know why sp_executesql is the better practice?

    Thanks,

    Dave

  • January 17, 2013 at 7:21 am

    #1578310

    Because it allows for parameterisation of the dynamic SQL (which exec does not). That leads to better plan reuse and better security

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2013 at 8:01 am

    #1578321

    Thank you, Gail. I just learned something good that I will use often.

    The subtle but significant differences make all the difference in the world.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • January 17, 2013 at 8:10 am

    #1578328

    GilaMonster (1/17/2013)

    Because it allows for parameterisation of the dynamic SQL (which exec does not). That leads to better plan reuse and better security

    Thanks for the answer!

    Does this apply only to executing sql from an application then?

    If I have a stored procedure that accepts parameters and build an sql string within the stored procedure, does it matter how the sql is executed in the stored procedure?

    I'm guessing, in this case, there is no security difference.

  • January 17, 2013 at 8:17 am

    #1578338

    Dave62 (1/17/2013)

    Does this apply only to executing sql from an application then?

    No.

    If I have a stored procedure that accepts parameters and build an sql string within the stored procedure, does it matter how the sql is executed in the stored procedure?

    Yes. Same reasons.

    I'm guessing, in this case, there is no security difference.

    Wanna bet?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2013 at 8:27 am

    #1578347

    Thanks for the answers! I'd much rather learn the best practice by asking here instead of finding out the hard way. 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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