Case in the where clause? - 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 the where clause?

Author

Topic

sherpa99
Starting Member

20 Posts

Posted - 2005-04-11 : 15:43:18

I am trying to dynamically build a statment using Case syntax, but
I really don't understand why I am getting the error
"Incorrect syntax near the keyword 'CASE'"

Can I not "append" to the where clause of this statment?

CREATE PROCEDURE members_byAge_get
(
@mode int
)
AS

SELECT email, dob

FROM members
WHERE DATEDIFF(day, dob, getdate())/365
CASE @mode
When 1 THEN '<= 17'
When 2 THEN 'BETWEEN 18 AND 25'
When 3 THEN 'BETWEEN 26 AND 29'
When 4 THEN 'BETWEEN 30 AND 39'
When 5 THEN 'BETWEEN 40 AND 49'
When 6 THEN 'BETWEEN 50 AND 59'
ELSE '>=60'
END

GO

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-11 : 16:07:42

You are attempting a form of "Dynamic SQL".

Non-dynamic version would be:

SELECT email, dob
FROM members
WHERE (@Mode = 1 AND (DATEDIFF(day, dob, getdate())/365 ) <= 17 )
OR (@Mode = 2 AND (DATEDIFF(day, dob, getdate())/365 ) BETWEEN 18 AND 25' )
OR etc .. etc ... etc

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 16:21:46

In case the dob column ever has an index on it, you should do something like this because its using the date dob column directly rather than a calculation based on it. Also, you won't have any ORs in the WHERE clause which will also force a tablescan.


declare @DtUpper dateTime
	,@DtLower dateTime


Select 	@dtUpper = 
		case @mode 
		when 1 then getDate()
		when 2 then dateAdd(year, -18, getDate())
		when 3 then dateAdd(year, -26, getDate())
		when 4 then dateAdd(year, -30, getDate())
		when 5 then dateAdd(year, -40, getDate())
		when 6 then dateAdd(year, -50, getDate())
		else dateAdd(year, -60, getDate())
		end
Select 	@dtLower = 
		case @mode 
		when 1 then dateAdd(year, -17, getDate())
		when 2 then dateAdd(year, -25, getDate())
		when 3 then dateAdd(year, -29, getDate())
		when 4 then dateAdd(year, -39, getDate())
		when 5 then dateAdd(year, -49, getDate())
		when 6 then dateAdd(year, -59, getDate())
		else 0
		end
--Select @dtLower, @dtUpper

SELECT email, dob
FROM members
Where	dob between @dtLower AND @dtUpper

Be One with the Optimizer
TG

sherpa99
Starting Member

20 Posts

Posted - 2005-04-11 : 16:54:11

wow ... I'm speechless and grateful ... such an eligant solution --
thanks a ton!

PW
Yak Posting Veteran

95 Posts

Posted - 2005-04-11 : 17:00:49

If "mode" is really an "Age Band Identifier", and if you commonly band results along these age boundaries, you could consider creating a small AgeBanding table with columns for lower & upper age limit, and join to this table in your queries. This would mean less SQL code to write, and any subsequent changes to how you group age can be accomplished with a simple data change instead of a code change.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources