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

case statement puzzle Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2010 12:57 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 1,333, Visits: 1,701
F1Droid (5/26/2010)
Good question that in its simplicity makes you question what seems the obvious answer and think about it more deeply.

According to SQL Books Online, the parser actually checks all conditions and returns the first true condition, if none then returns "else" if its coded otherwise NULL. That's how it seems to be described.


Yeah, I had the right answer in 5 seconds, but took another 3 minutes to answer the question because I was sure it couldn't be that simple...

As for the Books Online reference, anyone know of a good way to introduce a condition with side-effects so we could verify if it does indeed check all conditions?
Post #928556
Posted Wednesday, May 26, 2010 1:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
I was thinking it has to be true, but wait....... So I spent a few more minutes thinking, only to come to the same conclusion.
Post #928591
Posted Wednesday, May 26, 2010 2:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 27, 2013 6:58 PM
Points: 16, Visits: 35
The BOL seems to imply the 1 in "CASE 1" might be interpreted as a bit constant. Is this true? It seems 0/1 values are bit constants.

Note: It would not change the answer in this case due to the implicit conversion, but like others I was mistrusting and looking at it from all different angles. Just curious from an understanding standpoint.
Post #928627
Posted Friday, May 28, 2010 5:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 7, 2011 10:57 AM
Points: 3, Visits: 9
Thanks for explaining the example clearly. I answered it correctly but through some different logic. after your explaination, I understand my mistake
Post #929597
Posted Saturday, May 29, 2010 11:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 860, Visits: 1,401
The question was pretty simple.....! But I really liked it.

Since the @flg is defined with value = 1 and it is matching 1 again in Case statement so result should be obviously 'true'.

Nice question...keep posting more question.... :)
{{{{

Declare @flg as int

SEt @flg = 1

select case 1 when @flg then 'true'

when @flg then 'false'
else 'noflg'

}}}}
end as flag



Thanks.
Post #930113
Posted Tuesday, June 1, 2010 8:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Good question, thanks!

I'm actually a little surprised that most people got it correct.
Post #931067
Posted Tuesday, June 22, 2010 5:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Nice question!
Post #940929
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse