Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Dynamic Query Expand / Collapse
Author
Message
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
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
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: Thursday, April 17, 2014 11:58 AM
Points: 3,924, Visits: 1,588
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
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
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: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Posted Friday, May 01, 2009 8:25 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 7:41 PM
Points: 634, Visits: 399
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
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
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: Thursday, April 03, 2014 10:03 PM
Points: 262, Visits: 294
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
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: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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
Posted Monday, May 04, 2009 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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 12»»

Permissions Expand / Collapse