Assigning values to Variables with CASE

  • 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)

  • 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

  • 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. 🙂

  • 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

  • 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

  • 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

    🙂

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

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