Assigning values to Variables with CASE

Question

Assigning values to Variables with CASE

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 1

Personally, I love this type of question. I relates to some of the style of code I see day-to-day, sometimes with unclear, distracting, or obfuscating details.

I always read the code and try to figure out what it's going to do, answer the question, and then run the code afterwards.

If I get it wrong, or I want more detail, I read all of your comments.

In this case I got it wrong, but I learned something... or was at least reminded. And that's the point!

Thanks for the puzzle!

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Hugo Kornelis (4/3/2013)
I like the idea of the question - testing when an assignment to a variable with CASE would or would not overwrite previous assignments. I can see some people getting that wrong.
Unfortunately, the execution of the idea left to be desired. The code was long and complicated, making this question more an exercise in parsing code. The exact same question idea could have been done with a much shorter code sample:
DECLARE @Str1 varchar(10) = '',
@Str2 varchar(10) = '',
@val int;
SET @val = 1;
SELECT @Str1 = CASE WHEN @val = 1 THEN 'One' ELSE NULL END,
@Str2 = CASE WHEN @val = 2 THEN 'Two' ELSE NULL END;
SET @val = 2;
SELECT @Str1 = CASE WHEN @val = 1 THEN 'One' ELSE NULL END,
@Str2 = CASE WHEN @val = 2 THEN 'Two' ELSE NULL END;
SELECT @Str1, @Str2;
Sasidhar Pulivarthi, you obviously have good ideas for questions. I hope you will continue to submit question, and that you will take this feedback to try and convert your good ideas into even better questions going forward.

+1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 3

As a construct for simulating confused code, this question is a fine test.

I couldn't even begin to imagine what simplified version of real use-case this might have been.

If you found this snippet of code in real life then please write an article about what was going on, how you found this, and what you did to "fix" it. 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Since I mistakely thought (until I looked a bit harder) that the correct answer wasn't an option, I assumed the missing batch separator was a mistake and picked the 5 nulls and FF option (none of the others made any sense at all with that code); I imagine most people did either that or read the question properly the first time so that they knew the batch separator was irrelevant.

edit: I guess I'm getting too careless in my old age.

Tom

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 5

SQLRNNR (4/3/2013)
Hugo Kornelis (4/3/2013)
I like the idea of the question - testing when an assignment to a variable with CASE would or would not overwrite previous assignments. I can see some people getting that wrong.
Unfortunately, the execution of the idea left to be desired. The code was long and complicated, making this question more an exercise in parsing code. The exact same question idea could have been done with a much shorter code sample:
DECLARE @Str1 varchar(10) = '',
@Str2 varchar(10) = '',
@val int;
SET @val = 1;
SELECT @Str1 = CASE WHEN @val = 1 THEN 'One' ELSE NULL END,
@Str2 = CASE WHEN @val = 2 THEN 'Two' ELSE NULL END;
SET @val = 2;
SELECT @Str1 = CASE WHEN @val = 1 THEN 'One' ELSE NULL END,
@Str2 = CASE WHEN @val = 2 THEN 'Two' ELSE NULL END;
SELECT @Str1, @Str2;
Sasidhar Pulivarthi, you obviously have good ideas for questions. I hope you will continue to submit question, and that you will take this feedback to try and convert your good ideas into even better questions going forward.
+1

+1

Thanks

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Very good question. I like these kind of confusing codes and parsing in my mind. Though it gave some pain (in my head) solving this but was correct with the answer.

+1

🙂