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.

Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditional Where clause possible?

Is it possible to use a conditional statements in a where clause?

IE: I have 3 paramaters that may or may not be filled.

I would like to do something along the lines of...

Select * From (tables)

WHERE

If @.param1 has value

Begin

'run this where statement

if @.Param2 has value

'add this to the where clause

if @.param3 has value

'add this to the where cluase

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

thanks but I can't get to those websites...

Our company websense filters that out as "personal"

|||

Sometimes you can get away with something like:

Select * From (tables)

WHERE

(Field1 = @.param1 OR @.param1 IS NULL) AND

(Field 2 = @.param2 OR @.param2 IS NULL) AND ...

|||

Then tell your IT department that they are actually work related and why and ask them to allow access to them.

Simple as that.

|||

Mainiac007,

You can't do conditional code in T-SQL (unlike PL/SQL). You can, however, do this:

Select*From(tables)

where

col1 =coalesce(@.param1, col1)

and col2 =coalesce(@.param2, col2)

and...

Ron