April 2, 2013 at 10:27 pm
Comments posted to this topic are about the item Assigning values to Variables with CASE
April 2, 2013 at 11:22 pm
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 !!!
April 3, 2013 at 1:07 am
Jesus, i need my coffee 😉 (Failed)
April 3, 2013 at 2:26 am
An object lesson in the need for well-commented code!
April 3, 2013 at 3:00 am
This was removed by the editor as SPAM
April 3, 2013 at 3:23 am
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.
April 3, 2013 at 3:31 am
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.
April 3, 2013 at 3:41 am
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:
April 3, 2013 at 3:56 am
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
April 3, 2013 at 4:31 am
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.
April 3, 2013 at 5:05 am
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
April 3, 2013 at 5:40 am
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 🙂
April 3, 2013 at 7:13 am
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.
April 3, 2013 at 7:53 am
(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.
April 3, 2013 at 8:02 am
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 20 total)
You must be logged in to reply to this topic. Login to reply