Case Function - 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 2005 Forums

Transact-SQL (2005)

Case Function

Author

Topic

amitsyadav
Starting Member

15 Posts

Posted - 2007-12-21 : 02:20:40

We have "If" to check the condition. Can I use "CASE" instead of "IF". Actually i am getting an error in the following piece of code:
Case @Enum
When 1 then 'hello'
WHEN 2 THEN 'hello1'
End

Please help as I am not getting reason behind this.
Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 02:25:18

1 What is the error you are getting?
2 Can you post the full query you used?

Madhivanan

Failing to plan is Planning to fail

amitsyadav
Starting Member

15 Posts

Posted - 2007-12-21 : 02:33:36

Actually I am trying to create a common SP for our developers to fill their drop down list. And for this, they will pass an Enum # and get data accrodingly.

Query is:
ALTER PROCEDURE [dbo].[uspFillDropDown]
@Enum int
AS
BEGIN
SET NOCOUNT ON;
Case 1
When 1 then Select id, name from TableABC
WHEN 2 THEN 'hello1'
End
End

Error is:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Case'.

Please tell me the reason of this error also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 02:52:29

ALTER PROCEDURE [dbo].[uspFillDropDown]
@Enum int
AS
BEGIN
SET NOCOUNT ON;
Case @Enum
When 1 then Select id, name from TableABC
WHEN 2 THEN 'hello1'
End
End

amitsyadav
Starting Member

15 Posts

Posted - 2007-12-21 : 02:55:59

Error is same i.e.
Msg 156, Level 15, State 1, Procedure uspFillDropDown, Line 7
Incorrect syntax near the keyword 'Case'.
Msg 156, Level 15, State 1, Procedure uspFillDropDown, Line 9
Incorrect syntax near the keyword 'WHEN'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 02:58:51

Try this:-

ALTER PROCEDURE [dbo].[uspFillDropDown]
@Enum int
AS
BEGIN
SET NOCOUNT ON;
Case
When @Enum= 1 then (Select id, name from TableABC)
WHEN @Enum = 2 THEN 'hello1'
End
End

amitsyadav
Starting Member

15 Posts

Posted - 2007-12-21 : 03:05:03

still not working... getting same error.. :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 03:17:30

[code]ALTER PROCEDURE [dbo].[uspFillDropDown]
(
@Enum int
)
AS

SET NOCOUNT ON

IF @Enum = 1
Select id, name from TableABC
ELSE
IF @Enum = 2
SELECT 'hello1'[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 03:33:28

quote:
Originally posted by visakh16

Try this:-

ALTER PROCEDURE [dbo].[uspFillDropDown]
@Enum int
AS
BEGIN
SET NOCOUNT ON;
Case
When @Enum= 1 then (Select id, name from TableABC)
WHEN @Enum = 2 THEN 'hello1'
End
End

You cant return a recordset in a CASE expression

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 03:33:53

I think problem is with return values. The values returned by all branches of case should be of same data type. Do like this:-

ALTER PROCEDURE [dbo].[uspFillDropDown]
@Enum int
AS
BEGIN
SET NOCOUNT ON;
Case
When @Enum= 1 then (Select name from TableABC)
WHEN @Enum = 2 THEN 'hello1'
End
End

also ensure that subquery returns only one row. Else i think you have to go with IF.

amitsyadav
Starting Member

15 Posts

Posted - 2007-12-21 : 03:49:12

Thanks everyone. Now I have decided to go with IF statement.
Thanks again.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-21 : 03:51:26

You might also find this reading useful.
http://weblogs.sqlteam.com/jeffs/archive/2007/05/03/60195.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources