SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Query


Dynamic Query

Author
Message
Manish Sinha
Manish Sinha
Right there with Babe
Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)Right there with Babe (719 reputation)

Group: General Forum Members
Points: 719 Visits: 98
Comments posted to this topic are about the item Dynamic Query
Navi's
Navi's
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 1211
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' [color=#ff0000]--> Command(s) completed successfully[/color].
--1
SELECT @query = 'SELECT ''TEST'' ' [color=#ff0000]-->TEST[/color]
EXEC (@query)
--2
SELECT @query = 'SELECT ' + '''TEST''' [color=#ff0000]-->TEST[/color]

EXEC (@query)
--3
SELECT @query = 'SELECT ' + @variable [color=#ff0000]-->Missing end comment mark '*/'.[/color]
EXEC (@query)
--4
SELECT @query = 'SELECT ' + '''' + @variable + '''' [color=#ff0000]--> Command(s) completed successfully[/color].
EXEC (@query)
--5
SELECT @query = 'SELECT ' + ''' + @variable + ''' [color=#ff0000]Unclosed quotation mark after the character string ' + @variable'.[/color]
EXEC (@query)





Only 2 and 3 is the right answer

Navi's:-)
SanjayAttray
SanjayAttray
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8459 Visits: 1619
Navi's (5/1/2009)
Hi,

DECLARE @query AS VARCHAR(20)
DECLARE @variable AS VARCHAR(5)

SELECT @variable = 'TEST' [color=#ff0000]--> Command(s) completed successfully[/color].

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
Navi's
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 1211
Ohh!! got my mistake...

Thanks

Navi's:-)
StarNamer
StarNamer
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 1992
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
Michael Poppers
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1489 Visits: 416
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
mrcnkc
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 54
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
antony-688446
Say Hey Kid
Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)Say Hey Kid (688 reputation)

Group: General Forum Members
Points: 688 Visits: 371
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
Atif-ullah Sheikh
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8059 Visits: 5217
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 here


Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34646 Visits: 13126
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: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search