Select Case in sql server?

[jsteph]

Hi all,
I *have* tried the "Case When..." statement, but it does not seem to do what I want--I'm looking for something like the VB SELECT CASE block, and the Case/When seems to behave in a fundamentally different way.

Here is pseudocode using IF/ELSE of what I want--However I want to do this *without* IF/ELSE logic:

IF @variable = 'A'
   UPDATE tbl_A set field = 1
Else If @variable = 'B'
    UPDATE tbl_B set field = 1
Else If @variable = 'C'
    UPDATE tbl_C set field = 1
Else
    UPDATE tbl_X set field = 1;

The Case/When logic seems to want to return a value based on testing an expression, I want to perform an action.

The If/Else logic works, but it seems bulky. Is there a simpler way?
Thanks,
--Jim

 

[markros]

No, there is no other way. T-SQL doesn't have CASE statements controlling programming flow like VF/C/other languages do. So, the above code is the best you can have.

 

[jsteph]

Thanks...that's what I was afraid of...
--Jim

 

[bigfoot]

jsteph, don't give up.
Rethink it. What are you trying to do?

markros is correct but that doesn't mean you can't get it to work.
The @variable you are trying to use, does it come from a table? You can return a foo value then use that to decide what table to update.

Or simply use IF statements. They are cleaner in a stored procedure anyway.

Also, the case must be used as part of a select, but that doesn't mean the select needs to come from a table. It can select from a memory variable table too.


(I'm an old vb guy)

 

[t1hodges]

Has any1 ever created a select case statement using the IN function.

For example, and this code block doesn't work:

Public Function test()
a = "everything"
Select Case a
Case is = in("something","everything","nothing")
Debug.Print "TRUE"
Case Else
Debug.Print "FALSE"
End Select
End Function

 

[markros]

Is it VB.NET or VB code? You may ask in the appropriate forum this question then.

 

[t1hodges]

this is VBA

 

[t1hodges]

Actually got it to work. simply take OUT the in function.

Public Function test()
a = "everything"
Select Case a
Case is = "something","everything","nothing"
Debug.Print "TRUE"
Case Else
Debug.Print "FALSE"
End Select
End Function