Executing Dynamic SQL

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

  • nice question.

  • Thanks, nice one-ish

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Good question and equally good explanation. 🙂

  • 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

    🙂

  • 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

  • Nice and easy today - thanks

    Knew the answer to this through bitter experience 🙂

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There 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

  • 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]

  • 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 🙂

  • Stuart Davies


    Nice and easy today - thanks

    Knew the answer to this through bitter experience Smile

    +1.

  • 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.

  • Nice one..

    Used it frequently

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

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

  • 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'.

  • nice question.

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

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