Assigning values to Variables with CASE

  • Pulivarthi Sasidhar

    SSCertifiable

    Points: 6706

    Comments posted to this topic are about the item Assigning values to Variables with CASE

  • Danny Ocean

    SSCertifiable

    Points: 6098

    Good example. But rather than creating six different variables, i would like to create only one variable and use sp_executesql.

    Anyway. Thanks for question.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4891

    Jesus, i need my coffee 😉 (Failed)

  • Toreador

    SSChampion

    Points: 11242

    An object lesson in the need for well-commented code!

  • This was removed by the editor as SPAM

  • call.copse

    SSCoach

    Points: 16917

    Toreador (4/3/2013)


    An object lesson in the need for well-commented code!

    +1

    Just a little help with the intention of some of the string manipulation might make the point clearer.

  • call.copse

    SSCoach

    Points: 16917

    Toreador (4/3/2013)


    An object lesson in the need for well-commented code!

    +1

    Just a little help with the intention of some of the string manipulation might make the point clearer.

  • demonfox

    SSCertifiable

    Points: 6289

    The case statement will assign 'FF' to @str6 ,Remaining varaibles contains NULL values by default and not assigned with corresponding values of AA,BB,CC,DD and EE. The values are assigned to variables after completion loop.

    It looked like variables overwrite to me... and I didn't see any reference suggesting otherwise.

    At the start of the loop , @str1 is assigned a value AA , and then with the next cycle gets assigned to NULL . At the end of the loop all ,@STR* , becomes NULL but @str6; it is FF and didn't get to see the next loop.

    In the next loop, it gets interesting, as the NULLIF and ISNULL comes into the use; the result output becomes constant for string @cmpstr ,i.e. FF until any number of loop execution.

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Explanation could have been a little bit better.

    And question a little bit shorter 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thank you for the effort and question. I agree that the question could have been written more clearly/shorter, but you are on the right track. Please submit more questions.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • alex.d.garland

    Right there with Babe

    Points: 749

    Hugo Kornelis (4/3/2013)...The code was long and complicated, making this question more an exercise in parsing code....

    ...which personally I quite enjoyed in this instance, but agree that Hugo's recommendations to clean the question up are valid.

    Another vote for more questions from the OP 🙂

  • mtassin

    SSC-Insane

    Points: 23096

    Koen Verbeeck (4/3/2013)


    Explanation could have been a little bit better.

    And question a little bit shorter 🙂

    /agree

    Not even sure what purpose that function was supposed to solve.

    If it was to split the string... well there are better ways to do that.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    (EZ PZ... steps-involved; copy; paste; ctrl+e; select-answer; submit; grin; tongue.)

    Awesome; really nice question.

    At first; i noticed was not - only one insert but the variable initialization is happening in the loop of 6 runs... (i have one similar issue (with case) few days ago and I was not sure if this was the same) I took the guessing way 🙁 (rather being sure) - but it was correct. (and then I broke it down to compare what I had... and it turns out "lucky guess" had the upper hand...).

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    mtassin (4/3/2013)


    Koen Verbeeck (4/3/2013)


    Explanation could have been a little bit better.

    And question a little bit shorter 🙂

    /agree

    Not even sure what purpose that function was supposed to solve.

    If it was to split the string... well there are better ways to do that.

    (with no offence, and I see and agree what you mean - But still... I would rather take it has challenge. the perfect qtod is difficult to form for all, so when the details are not as it is needed or not able to figure it out what we are suppose to do.. i just go with the hunches and try to extract the intent from the question by breaking it down and see what author has to say and then... choose one, it actually helps our extraction and deduction skills... will.. this is just me.)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Viewing 15 posts - 1 through 15 (of 21 total)

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