Dynamic Query

  • Manish Sinha

    Ten Centuries

    Points: 1023

    Comments posted to this topic are about the item Dynamic Query

  • Navi's

    SSC Enthusiast

    Points: 157

    Hi,

    I ran given queries. and got only 2 and 3 are display result TEST.

    none of the other display any "TEST" some of displya Command Executed Successfully.

    DECLARE @query AS VARCHAR(20)

    DECLARE @variable AS VARCHAR(5)

    SELECT @variable = 'TEST' --> Command(s) completed successfully.

    --1

    SELECT @query = 'SELECT ''TEST'' ' -->TEST

    EXEC (@query)

    --2

    SELECT @query = 'SELECT ' + '''TEST''' -->TEST

    EXEC (@query)

    --3

    SELECT @query = 'SELECT ' + @variable -->Missing end comment mark '*/'.

    EXEC (@query)

    --4

    SELECT @query = 'SELECT ' + '''' + @variable + '''' --> Command(s) completed successfully.

    EXEC (@query)

    --5

    SELECT @query = 'SELECT ' + ''' + @variable + ''' Unclosed quotation mark after the character string ' + @variable'.

    EXEC (@query)

    Only 2 and 3 is the right answer

    [font="Arial Black"]Navi's:-)[/font]

  • SanjayAttray

    SSChampion

    Points: 13157

    Navi's (5/1/2009)


    Hi,

    DECLARE @query AS VARCHAR(20)

    DECLARE @variable AS VARCHAR(5)

    SELECT @variable = 'TEST' --> Command(s) completed successfully.

    Only 2 and 3 is the right answer

    SELECT @variable = 'TEST' is the value passed in variable.

    Answer is right for question. 1,2,4 are correct answers.

    Error on 3:

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'TEST'.

    Error on 5:

    Msg 105, Level 15, State 1, Line 1

    Unclosed quotation mark after the character string ' + @variable'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near ' + @variable'.

    SQL DBA.

  • Navi's

    SSC Enthusiast

    Points: 157

    Ohh!! got my mistake...

    Thanks

    [font="Arial Black"]Navi's:-)[/font]

  • StarNamer

    SSCrazy Eights

    Points: 8633

    Far too easy!

    Since 1 works, the answer had to include 1. Hence it had to be 1,2,4 without needing to actually check the others. 🙂

    Derek

  • Michael Poppers

    SSCrazy

    Points: 2119

    I found this Q too easy, as option 1 seemed correct and was listed in only one of the five possible answers for the Q, but I looked at the other options just to satisfy myself that options 2 and 4 were also correct :-).

  • mrcnkc

    Grasshopper

    Points: 24

    If you modify the code for the Select variable as follows.

    SELECT @variable = '''TEST'''

    Then 1,2,3 works and not 4 and 5

  • antony-688446

    Ten Centuries

    Points: 1221

    Feel cheated by this one, as the question should have been a 'Choose all that apply'. Making it multi-choice makes the answer obvious. This is really annoying when you have actually bothered to work out the correct options first!

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    Very Tricky...

    The last query DO NOT Execute due to the the @query variable declared as varchar(20)

    if You declare the @query variable as varchar(25), it executes.

    Nice...

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Atif Sheikh (5/4/2009)


    Very Tricky...

    The last query DO NOT Execute due to the the @query variable declared as varchar(20)

    if You declare the @query variable as varchar(25), it executes.

    Nice...

    It'll execute all right, but it'll NOT produce the requested result...


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    Oh Yes... You are absolutely Right...

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

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

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