CASE Transact SQL Reference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CASE Transact SQL Reference

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-20 : 07:31:38

Anatoliy writes "In example below same rules(conditions) apply to assign values to different fields. Is the any other(more elegant) way like using one conditional CASE statement to assign values to different fields.
Thank you

SELECT
CASE
WHEN <condition1> THEN <value1>
WHEN <condition2> THEN <value2>
WHEN <condition3> THEN <value3>
ELSE <value4>
END as 'field1',
CASE
WHEN <condition1> THEN <value5>
WHEN <condition2> THEN <value6>
WHEN <condition3> THEN <value7>
ELSE <value8>
END as 'field2',
CASE
WHEN <condition1> THEN <value9>
WHEN <condition2> THEN <value10>
WHEN <condition3> THEN <value11>
ELSE <value12>
END as 'field3' into <table2>
FROM <table1>"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-20 : 07:35:03

Looks pretty elegant to me. Since you have three columns you'll need three case expressions anyway. The only way to make this shorter is to set up a table that stores related values to the ones in table1. You can then JOIN that table to table1. The only problem is the type of logical condition you're testing. JOINs really only work on equality matches. If you have to do greater than, less than, etc. comparisons then this may not work for you.

Can you provide more detail in your code?

marat
Yak Posting Veteran

85 Posts

Posted - 2004-01-20 : 17:38:59

Yes it is pretty elegant while it fits into one page A4 format.
In reality if I provide code for conditions 1,2 and 3, it will take one A4 page for each CASE with code duplication on each page.
I think possibly I found this strange code because of not efficient DB model.
Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-20 : 23:58:00

You are right about the code duplication bit there. If you are using SQL Server 2000, you could encapsulate the conditions inside a User Defined Function, that'll ensure you have the central logic in one place and the SQL looks great too!

Consider this:

CREATE FUNCTION udf_ReturnAValue(param1 INT, param2 INT)
RETURNS INT
AS
BEGIN
DECLARE @ReturnValue INT

END

OS

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-21 : 00:06:07


CREATE FUNCTION udf_ReturnAValue(param1 INT, param2 INT)
RETURNS INT 
AS
BEGIN
  DECLARE @ReturnValue INT
  SELECT @ReturnValue = 
    CASE 
      WHEN param1 = 0 AND param2 = 1 THEN 1
      WHEN param1 = 1 AND param2 = 2 THEN 2
      WHEN param1 = 5 AND param2 = 10 THEN 3
    END

  RETURN @ReturnValue
END

And then in your stored procedure you could:


SELECT CASE dbo.ReturnAValue(FieldA, FieldB)
  WHEN 1 THEN SomeValue
  WHEN 2 THEN AnotherValue
  WHEN 3 THEN SomeOtherValue
  END AS Field1,
...
--you get the drift

If you really want it clean then you could include the second set of CASE statements inside the UDF as well.

OS

marat
Yak Posting Veteran

85 Posts

Posted - 2004-01-21 : 17:27:12

Dear mohdowais,
Things became clear. If we have code repetition the best way is to create a function, this is common development practice.
Because I am new to MS T-SQL I was thinking about CASE ability to work as: IF <condition> THEN DO: <do something> END.
Wow: It makes me think of another possible way:
SELECT
CASE
WHEN <condition1> THEN <value = <run function to assign values to
field1,field2,field3>>
WHEN <condition2> THEN <value> = <run function to assign values to
field1,field2,field3>>
WHEN <condition3> THEN <value> = <run function to assign values to
field1,field2,field3>>
Will it work???
Sorry, because I have dev. background I still think in dev. categories.

Thank you mohdowais

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-01-22 : 04:29:01

Oh yes that will do as well, as long as you have encapsulated as much of your logic in functions as possible. I can't tell you how much heartache this can save you in the long run.

OS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-22 : 08:51:45

No, that will not work. What are you trying to? What are these conditions and why do they affect 3 columns? what kind of data are we talking about here. I suspect you're doing things in a manner much more difficult than you need to.

Please give us some background info. But keep in mind that for all purposes, CASE is a function not a control-of-flow command. Pretend the format is CASE (WHEN cond THEN val1 ELSE val2 END) (kind of like a "super-function" !) and it might make a little more sense to a non-T-SQL programmer.

That is, it just returns 1 single value -- it cannot assign values to different columns in a resultset or anything like that.

- Jeff

marat
Yak Posting Veteran

85 Posts

Posted - 2004-01-23 : 01:14:42

Hi Jeff,
the sample of code I supplied isn't mine.
The first thing came to my mind was how to avoid repetition of big chanks of code in the 'CASE' switch.
But as you said
"... keep in mind that for all purposes, CASE is a function not a control-of-flow command."
So in that case 'CASE' shouldn't be a case.
Problem should be in totally different area, but I think this is
absolutely different story. May be I will return to it at some time later.
Thanks to you all guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources