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
)
ASselect 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 nullelse 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.
