Using CASE statement to build different where clause

From: NWx (test_at_test.com)
Date: 03/16/04 

Date: Wed, 17 Mar 2004 01:25:57 +0200

HI,

Is it possible to use CASE statement to select a different where clause in a
SQL statement?

What I try to accomplish is this:

I have a table, with a smalldatetime field

I want to create a stored proc, which can receive two parameters, start and
end date
If parameters are present (not null I suppose), I want to select records
between those dates.
Otherwise I want to select all records

My stored proc looks as follows:

ALTER Procedure GetNotifications

(

@StartDT smalldatetime,

@EndDT smalldatetime

)

As

set nocount on

select * from Notifications

CASE

when @StartDT IS NOT NULL

where notify_date between @StartDT and @EndDT

END

return

But I cannot save it, I get the following error (in Access ADP)

"Incorrect syntax near the keyword 'CASE'"

How can I accomplish what I need?

Thank you

Relevant Pages