How about the (admittedly unimpressively) simple

IF @cat IS NULL
BEGIN
Select *
From P2_TaskGroups
Where TaskGrp_Status = @Status
END
ELSE
BEGIN
Select *
From P2_TaskGroups
Where TaskGrp_Status = @Status
And TaskGrp_Category = @Cat
END

> -----Original Message-----
> From: Parag Kulkarni [SMTP:[Email Address Removed] Sent: Tuesday, September 24, 2002 7: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