how to do case statements outside of queries

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)

how to do case statements outside of queries

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 12:39:33

I want to do a case statement instead of if...else. However, all I can find on the Interweb is examples with case mixed in with sql queries. Here's what I have:

CASE

When @variable = 'HI' then @variable2 = @variable3;
when @variable = 'Bye then @variable2 = @variable3;
else 'Not Valid';

I get the error "Incorrect syntax near the keyword 'CASE'". Is it not possible to use case statements this way in T/SQL?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 12:41:34

select
CASE
When @variable = 'HI' then @variable2 = @variable3
when @variable = 'Bye then @variable2 = @variable3
else 'Not Valid'

you don't need ;
you must put it in a select statement since you're doing variable setting anyway.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 12:48:13

yea, I know. It's an asthetic choice.

as far as the select statement, I tried it and it gives me an incorrect syntax near '=' error for the first When statement.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 12:51:14

no i mean you can't have ; inside the case:
select
CASE
When @variable = 'HI' then @variable2 = @variable3
when @variable = 'Bye then @variable2 = @variable3
else 'Not Valid'
END

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 12:54:43

thanks. unfortunately, i get a syntax error for END and it still complains about the '=' sign

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 12:56:24

didn't see you were assigning the variable inside the then part.
you can't do that:

select
@variable2 =
CASE
When @variable in ('HI', 'Bye') then @variable3
else 'Not Valid'
END

can you provide your full statement?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 13:09:50

sure.

It's in a procedure, of course. I'll remove the semicolons. Let me make a slight modification, as to better reflect my code.

Declare @variable varchar (10)

Declare @variable2 int
Declare @variable3 int
Declare @variable4 int

--fyi, spirit1, @variable gets assigned a string value from a database lookup. Actually it can get assigned one of a select
--few. @variable4 gets assigned a specific integer value from a database lookup.

Select
Case
When @variable = 'HI' then @variable2 = @variable4
when @variable = 'Bye then @variable3 = @variable4
else 'Not Valid'
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 13:20:46

ok your query doen't really make sense.

to what variable do you set 'Not valid'?

you can do this:


Declare @variable varchar (10);
SELECT @variable = 'b'
Declare @variable2 int;
Declare @variable3 int;
Declare @variable4 int;

SELECT
@variable2 = CASE WHEN @variable = 'HI' then @variable4 ELSE -1 END,
@variable3 = CASE WHEN @variable = 'Bye' then @variable4 ELSE -1 END 

SELECT @variable2, @variable3

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 13:31:43

you're right. Yeah, I thought it would complain without an else condition, so I put one in. I really like your example here.

Unfortunately, I should've mentioned earlier that the case statement is contained inside a while loop. What I meant to say originally was while variable4 ges assigned a specific integer value from a database lookup, it's value will change throughout the lifetime of the while loop. Maybe case statements are not designed to handle multiple variables in a single expression.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 13:44:39

how about you show us the ENTIRE code that applies to this problem like i already asked?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-18 : 14:00:19

that was pretty much it. I cannot post the actual code in the forum, per my company's policy. I think I see what the problem is, though. using the '=' after the then keyword is not allowed. And Assigning multiple variables in one case statement is impossible because it wasn't designed that way; hence, the lovely code you wrote earlier with variable2 and variable3 receiving their own case statements. But I VERY MUCH appreciate your help and you've made me understand the conventions with case in sql server a lot better than prior to this discussion. Again, thank you very much!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 14:07:35

cool

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources