Please Help, Dynamic Search Stored Proc.

Please help, I am trying to write a dynamic search stored procedure using three fields.


I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic.


Can you please help me, construct a stored procedure.


User can choose any of the three(progno, projno, contractno) fields as a where condition.





I ma using asp.net as front end with sql server backend.








CREATE PROCEDURE dbo.USP_Searchrecords


(@ProgNO nvarchar(50),


@ProjNOnvarchar(50) ,


@ContractNOnvarchar(50))


AS


DECLARE @myselect nvarchar(2000)


DECLARE @psql nvarchar(2000)


DECLARE @strsql nvarchar(2000)


SET NOCOUNT ON





@psql = "SELECT * FROM Mytable"





IF @ProgNO <> '' then


strsql = WHERE ProgNO = @ProgNO


end if





If @ProjNO <> '' then


if strsql <> '' then


strsql = strsql & " and ProjNO =@ProjNO


ELSE


strsql = wHERE ProjNO =@ProjNO


END IF


END IF





If @ContractNO <> '' then


if strsql <> '' then


strsql = strsql & " and ContractNO =@ContractNO


ELSE


strsql = wHERE ContractNO =@ContractNO


END IF


END IF





@myselect = @psql + @strsql





EXEC(@myselect)








Please help. Thank you very much.