CASE STATEMENT - 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 STATEMENT

Author

Topic

kreg
Starting Member

7 Posts

Posted - 2004-09-08 : 23:41:40

TRYING TO TEST A SIMPLE VARIABLE VALUE IN A STORED PROCEDURE. BASED ON THE VARIABLE @COUNT I WANT A DIFFERENT SELECT STATMENT.. WHAT AM I MISSING? SHOULD I TRY TO USE A NESTED IF INSTEAD?

CASE
WHEN @COUNT > 5 THEN SELECT @ksg6 = Description FROM Features WHERE (Uid = @val)
WHEN @COUNT > 4 THEN SELECT @ksg5 = Description FROM Features WHERE (Uid = @val)
WHEN @COUNT > 3 THEN SELECT @ksg4 = Description FROM Features WHERE (Uid = @val)
WHEN @COUNT > 2 THEN SELECT @ksg3 = Description FROM Features WHERE (Uid = @val)
WHEN @COUNT > 1 THEN SELECT @ksg2 = Description FROM Features WHERE (Uid = @val)
WHEN @COUNT > 0 THEN SELECT @ksg1 = Description FROM Features WHERE (Uid = @val)
END

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-09 : 00:07:21

You can't do it this way using CASE.
SQL CASE statements are for in-line usage, not as you're used to in VB etc. You'd be better off with a nested IF in this case.

kreg
Starting Member

7 Posts

Posted - 2004-09-09 : 00:18:39

THANKS FOR THE QUICK RESPONSE.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-09 : 00:29:35

No problem......

Can I introduce you to your caps lock key? :-)

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-09-09 : 03:05:23

quote:

SQL CASE statements are for in-line usage, not as you're used to in VB etc.

CASE statements in SQL are used the same way as in VB. T-SQL does not support case statements, it only supports case expressions.

kreg
Starting Member

7 Posts

Posted - 2004-09-09 : 09:05:55

Anybody have a great nested if example they could share.. "Look no CAPS" ;)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-09 : 09:06:11

quote:
CASE statements in SQL are used the same way as in VB. T-SQL does not support case statements, it only supports case expressions.

Please explain...

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-09-09 : 10:39:16

SQL has support for both case statements and case expressions. Case statements and case expressions are used in different contexts.

A case statement is used for flow control like


case x 
    when 12 then
         insert into t1 values(1);
    when 13 then
         insert into t2 values(1);
end case;
-- the example uses PSM, the standard langauge for routines in SQL

A case expression is as the example in the first post. So what you call a case statement is actually a case expression. BOL uses the term case function which is okay, but a case expresssion is definetly not a statement.

kreg
Starting Member

7 Posts

Posted - 2004-09-09 : 10:55:12

So using your example, and this is T-SQL, I would write it like:

CASE @COUNT
WHEN > 5 THEN SELECT @ksg6 = Description FROM Features WHERE (Uid = @val);
WHEN > 4 THEN SELECT @ksg5 = Description FROM Features WHERE (Uid = @val);
END CASE;

I think this might still be an expression..?? But if your INSERT works, than my SELECT should, right?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-09-09 : 12:22:40

As T-SQL does not support case statements I don't see the point of the example.

I wouldn't call your example an expression.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources