Dynamic Query

  • Comments posted to this topic are about the item Dynamic Query

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

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

  • Ohh!! got my mistake...

    Thanks

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

  • 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

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

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

    SELECT @variable = '''TEST'''

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

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

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

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

  • 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 10 (of 10 total)

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