Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Manish Sinha » Dynamic Query
Add to briefcase
11 posts, Page 1 of 2
12»»

Dynamic Query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Manish Sinha
Posted Thursday, April 30, 2009 8:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 05, 2010 10:47 PM
Points: 93, Visits: 98
Comments posted to this topic are about the item Dynamic Query
Post #708234
Navi's
Posted Friday, May 01, 2009 7:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 20, 2011 8:15 AM
Points: 111, Visits: 1,211
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
Post #708430
SanjayAttray
Posted Friday, May 01, 2009 7:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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.
Post #708448
Navi's
Posted Friday, May 01, 2009 8:03 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 20, 2011 8:15 AM
Points: 111, Visits: 1,211
Ohh!! got my mistake...

Thanks

Navi's
Post #708465
Derek Dongray
Posted Friday, May 01, 2009 8:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946
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
Post #708480
Michael Poppers
Posted Friday, May 01, 2009 8:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:34 PM
Points: 565, Visits: 360
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 .
Post #708481
mrcnkc
Posted Friday, May 01, 2009 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 6:45 AM
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
Post #708492
antony-688446
Posted Sunday, May 03, 2009 6:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:26 PM
Points: 254, Visits: 277
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!
Post #709137
Atif-ullah Sheikh
Posted Monday, May 04, 2009 12:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960
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
Post #709191
Hugo Kornelis
Posted Monday, May 04, 2009 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 5,231, Visits: 7,021
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
Post #709244
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.