SQL SET VARIABLE USING CASE?

[Sweenster]

Hi, Not critical as I have a work around so this is just to help me learn/improve my code...

I would like to know if you can you set a variable when using CASE? I tried various ways but with no luck..I needed to add either 0 or 12 hours to a date based on whether the StartDate and EndDate matched...

I tried these two but failed!!...

CASE WHEN START_DATE = SESS_END_DATE THEN SET @Hours = 0 ELSE SET @Hours = 12 END
DATEADD(hh, @Hours, START_DATE)

and also..

SET @Hour = CASE WHEN START_DATE = SESS_END_DATE THEN 0 ELSE 12 END
DATEADD(hh, @Hours, START_DATE)

Any pointers would be appreciated..

 

[Crowley16]

no, you can only use the case clause as part of a select statement...

when using just variables, you need to be using if...

however, you can do something like:
select
@var = case
when someCondition then someValue
else someOtherValue
end

--------------------
Procrastinate Now!

 

[gmmastros]

I think you want something like this...

[tt][blue]Select DateAdd(hh, CASE WHEN START_DATE = SESS_END_DATE THEN 0 ELSE 12 END, START_DATE)[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

>> --when using just variables, you need to be using if...

This is not exactly true, but you do need to be careful.

[COLOR=blue]Declare[/color] @ShoeSize [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @BigFoot [COLOR=blue]varchar[/color](20)

[COLOR=blue]Set[/color] @ShoeSize = 9

[COLOR=blue]Select[/color] @BigFoot = ([COLOR=blue]Select[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] @ShoeSize Between 0 and 4 [COLOR=blue]Then[/color] [COLOR=red]'Tiny'[/color]
                               [COLOR=blue]When[/color] @ShoeSize Between 5 and 7 [COLOR=blue]Then[/color] [COLOR=red]'Small'[/color]
                               [COLOR=blue]When[/color] @ShoeSize Between 8 and 10 [COLOR=blue]Then[/color] [COLOR=red]'Normal'[/color]
                               [COLOR=blue]When[/color] @ShoeSize Between 11 and 13 [COLOR=blue]Then[/color] [COLOR=red]'Large'[/color]
                               [COLOR=blue]Else[/color] [COLOR=red]'Big Foot'[/color]
                               [COLOR=blue]End[/color])
[COLOR=blue]Select[/color] @BigFoot

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

If you must use a variable for some reason you can use a case stament but use selct instead of set like so:

SElect @Hour = CASE WHEN START_DATE = SESS_END_DATE THEN 0 ELSE 12 END
  from table1

As you wrote the code, how would you know what the value of start_date was because you weren't referncing a table, view or variable.

Questions about posting. See faq183-874

 

[Sweenster]

The workaround I had used was as George suggested and gets the jobs done fine. This is part of a large select qry so for neatness I was just hoping I might be able to replace lots of occurances of

DateAdd(hh, CASE WHEN START_DATE = SESS_END_DATE THEN 0 ELSE 12 END, START_DATE)

with..
DateAdd(hh, @Variable, START_DATE)

But it looks like you can't combine a SELECT statement that assigns a value to a variable with data-retrieval operations.

Thanks for your time everyone.