Executing Dynamic SQL

  • ksatpute123

    Hall of Fame

    Points: 3325

    Comments posted to this topic are about the item Executing Dynamic SQL

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    nice question.

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    Thanks, nice one-ish

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • sqlnaive

    SSCoach

    Points: 17435

    Good question and equally good explanation. 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21711

    Be aware of case:

    DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR

    It should be:

    DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE sp_executesql @STR

    🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258907

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Stuart Davies

    SSCoach

    Points: 18813

    Nice and easy today - thanks

    Knew the answer to this through bitter experience 🙂

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • etsav

    Old Hand

    Points: 308

    It should be underlined that this

    [font="Courier New"]DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR[/font]

    works on MSSQL 2008 -->

    On MSSQL 2005 it returns

    [font="Courier New"]Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.[/font]

    Equivalent for 2005 is:

    [font="Courier New"]DECLARE @STR VARCHAR(8000)

    SET @STR = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR[/font]

  • Toreador

    SSChampion

    Points: 11225

    ksatpute123 (9/25/2013)


    Comments posted to this topic are about the item <A HREF="/questions/Dynamic+SQL/102252/">Executing Dynamic SQL</A>

    Nice idea, but not sure about the execution. I knew it was going to fail due to the data type, but any of the options was feasible so it came down to a guess as to which one the MS developers decided to return. Luckily I guessed right 🙂

  • ksatpute123

    Hall of Fame

    Points: 3325

    Stuart Davies


    Nice and easy today - thanks

    Knew the answer to this through bitter experience Smile

    +1.

  • ksatpute123

    Hall of Fame

    Points: 3325

    etsav (9/26/2013)


    It should be underlined that this

    [font="Courier New"]DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR[/font]

    works on MSSQL 2008 -->

    On MSSQL 2005 it returns

    [font="Courier New"]Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.[/font]

    Equivalent for 2005 is:

    [font="Courier New"]DECLARE @STR VARCHAR(8000)

    SET @STR = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR[/font]

    Thanks for adding this. Lot of guys I know have directly started working from 2008. This is helpful for them.

  • psingla

    Hall of Fame

    Points: 3840

    Nice one..

    Used it frequently

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Carlo Romagnano (9/26/2013)


    Be aware of case:

    DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE SP_EXECUTESQL @STR

    It should be:

    DECLARE @STR VARCHAR(8000) = N'SELECT * FROM sys.objects'

    EXECUTE sp_executesql @STR

    🙂

    Only if the instance is case sensitive 🙂

    In that case the correct answer would be

    Msg 2812, Level 16, State 62, Line 3

    Could not find stored procedure 'SP_EXECUTESQL'.

  • PRAMANA.DBA

    SSCertifiable

    Points: 5507

    nice question.

Viewing 15 posts - 1 through 15 (of 27 total)

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