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


Dynamic Query


Dynamic Query

Author
Message
Manish Sinha
Manish Sinha
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 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
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2808 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 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
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5188 Visits: 5204
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19189 Visits: 12426
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