List , I got the solution with use of dynamic SQL.Here is the code for my inetersted freinds.Thanks all for the help.

CREATE Procedure SPT
@Status Int=Null,
@Cat Int=Null
As
Begin
declare @strbasic nvarchar(150), @str nvarchar(100)

set @strbasic = 'Select * From P2_TaskGroups'
set @str = null
if @str = null and (@Status is not null or @Cat is not null)
begin
set @str = ' where'

if @Status <> null
set @str = @str + ' taskgrp_status = ' + convert(varchar,@Status)
if @Cat <> null and @Status <> null
set @str = @str + ' and taskgrp_category = ' + convert(varchar,@Cat)
if @Cat <> null and @Status = null
set @str = @str + ' taskgrp_category = ' + convert(varchar,@Cat)
set @str = @strbasic + @str
end
else
set @str = @strbasic
--print @str
exec sp_executesql @str


End
GO

Parag Kulkarni
wrote:
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!


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