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

Rocko
Starting Member

26 Posts

Posted - 2006-11-29 : 16:24:11

Hi,

I have a stored procedure with two select statements. The only dereference between two statements is the following expression:


and PriorityID not in(26)

I was wondering how I can use only one select statement using case instead of two as it is now:


CREATE PROCEDURE [dbo].[wsp_GetPriority]
(
  @serviceCode varchar(5)
)
	
AS
SET NOCOUNT ON
	if @serviceCode not in ('CP','CPA','CPAS','CTR','CTA','CTAS')
	
		BEGIN
			Select PriorityID, Priority
			From Priority
			Where Active = 1
			and PriorityID not in(26)
			Order by Priority
		END
	
	ELSE
		BEGIN
			Select PriorityID, Priority
			From Priority
			Where Active = 1
			Order by Priority
		END
GO

Thanks in advance for your help!
Rocko

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:40:48

Try this

SELECT		PriorityID,
		Priority
FROM		Priority
WHERE		CASE
			WHEN @ServiceCode IN ('CP', 'CPA', 'CPAS', 'CTR', 'CTA', 'CTAS') THEN 1
			WHEN PriorityID <> 26 THEN 1
			ELSE 0
		END = 1
		AND Active = 1
ORDER BY	Priority

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 16:53:45

Please do not change original query after answered.

Peter Larsson
Helsingborg, Sweden

Rocko
Starting Member

26 Posts

Posted - 2006-11-29 : 17:17:57

Thanks Peso,

I didn't change the query. I just found a typo in my original post.

Your query is working fine.
However Iâ€™m not sure how the expression is working. Could you explain little more please?

CASE
			WHEN @ServiceCode IN ('CP', 'CPA', 'CPAS', 'CTR', 'CTA', 'CTAS') THEN 1
			WHEN PriorityID <> 26 THEN 1
			ELSE 0

Thanks
Rocko

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 17:31:01

CASE statement is one the the few thing in SQL Server you can rely on that are executed sequentially.

If @ServiceCode is any of the depicted codes (your case 2), the CASE statement is set to 1, and then returns.
If @ServiceCode fails first check, the code is NOT any of the depicted codes (your case 1), I also check for PriorityID is not equal to 26. If so, set CASE statment to 1 and return
In all other cases, set CASE to 0 and return.

To round things up, filter complete CASE statement if equal to 1 in the WHERE clause.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources