Conditonal WHERE clause

Hi,

I have a sproc, called spGetJobs, which is querying a table called Jobs. Jobs are either filled or not filled. If filled, the DateJobFilled field will have a date value. If not filled, that field is null. The sproc takes a parameter to indicate either take all jobs or only unfilled jobs. I tried to solve this with a CASE statement in the WHERE clause, as in the following:

ALTER PROCEDURE dbo.spGetJobs
(
@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs
@.StartDate smalldatetime
)
AS

select j.JobID, c.ClientID, j.JobStart, j.JobEnd
from Jobs j
join Clients c on j.ClientID = c.ClientID
where j.JobStart >= @.StartDate
and j.Role = 'client'
and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

However, VS complains of a syntax error when I try to save this.

I suppose I could construct the SELECT statement as a string and then execute it, but would rather not have to do that. Any suggestions as how to make a conditional where clause?

Thanks.

I think the problem is when your AND clause here:

Code Snippet

and (case when @.UnfilledJobs = 1 then j.JobFilledDate is not null

else 1 = 1 end)

What are you trying to accomplish with this clause? Maybe you need something like this?

Code Snippet

and ( @.UnfilledJobs = 1 and j.JobFilledDate is null or
@.unfilledJobs = 0
)

or maybe:

Code Snippet

and ( j.JobFilledDate is null or @.unfilledJobs = 0 )

|||

For Better performance use the if .. else statement; You can avoid the table scan,

Code Snippet

ALTER PROCEDURE dbo.spGetJobs

(

@.UnfilledJobs bit, -- if 1, get only unfilled jobs, else all jobs

@.StartDate smalldatetime

)

AS

If @.UnfilledJobs = 1

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

and j.JobFilledDate is not null

else

select j.JobID, c.ClientID, j.JobStart, j.JobEnd

from Jobs j

join Clients c on j.ClientID = c.ClientID

where j.JobStart >= @.StartDate

and j.Role = 'client'

|||

Thanks. Your second code snippet did the trick. I had previously considered the if..else construct suggested by the next message, but the query is actually much more complex than what I posted (I stripped out all the unnecessary joins to simplify the issue) and I really don't want to repeat the entire query. Also, I don't think performance will be a significant issue here.