Case in Where 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 in Where statement

Author

Topic

elomon
Starting Member

37 Posts

Posted - 2007-04-16 : 14:49:12

I am trying to build a stored procedure where I pass in a value and have the where clause change based on the passed value. Here is what I have, which does not work. I have been testing by checking the syntax in Query Analyzer; the tables are not real. Thank you:

Create Procedure usp_ThisThat
@SevID tinyint=Null
As
Select SomeStuff
From ThisTable
Where Active=1
AND Severity IN Case
WHEN (@SevId=7) THEN (5,10)
WHEN (IsNull(@SevID,0)=0) THEN (1,5,10)
Else @SevID
END

Fails on 'Incorrect syntax near the keyword 'Case'. Any ideas? I cannot do a 'And Severity='.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-16 : 14:51:48

Try it this way:

SELECT SomeStuff
FROM YourTable
WHERE Active = 1 AND
((@SevID = 7 AND Severity IN (5, 10)) OR
(@SevID IS NULL AND Severity IN (1, 5, 10)) OR
(@SevID IS NOT NULL AND @SevID != 7 AND Severity = @SevID))

SQL Server Helper
http://www.sql-server-helper.com

elomon
Starting Member

37 Posts

Posted - 2007-04-16 : 15:21:26

Perfect, thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources