The first thing that comes to mind if you need to get this done right away
is to interrogate the variables inside the procedure, and if 1 or both are
null set them to a value of '%' and in the query use the like clause. So if
@cat is null then the query would be and
TaskGrp_Category like '%'

Not the best solution because the like clause can really slow things down,
but it's quick and dirty.
Alternatively, you can interrogate the variables and based on their values,
send control to a certain position in the procedure with a query built to
handle that combination of variables. Also not very server friendly, but
both fast to set up.


-----Original Message-----
From: Parag Kulkarni [mailto:[Email Address Removed]
Sent: Tuesday, September 24, 2002 12:57 PM
To: LazyDBA.com Discussion
Subject: How to nullify condition in WHERE Clause.


Hello Everyone,

Does anyone know how to nullify a condition in "Where Clause".


I am working on search functionality and need to write a stored procedure in
SQL 2000. The SP will have code like below.

Select *
From P2_TaskGroups
Where TaskGrp_Status = @Status
And TaskGrp_Category = @Cat

So If the @cat is not passed then the query should not consider the AND
condition and return the results based on the passed status or vice versa.

What is happening right now is I have @Status Int= Null, @Cat Int= Null as
Input parameters to this SP and if only status is passed this SP returns no
rows where as I need to get rows based on status only.

Has anyone done this kind of code before or know how to do it.

Many Many Thanks,

Parag
















---------------------------------
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
MS Sql Server LazyDBA home page