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


Dynamic Query


Dynamic Query

Author
Message
Manish Sinha
Manish Sinha
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

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

Group: General Forum Members
Points: 111 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4383 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 (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

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

Thanks

Navi's:-)
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 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
SSC Eights!
SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)SSC Eights! (827 reputation)

Group: General Forum Members
Points: 827 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 (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3742 Visits: 5189
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10930 Visits: 11981
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 MVP
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