How to pass parameter values in MS SQL 2008 SP to conduct search query on only passed parameter values

THx...could you give me an ex of the if else logic.. Glen

Sure

create procedure test @Test1 int = null, @Test2 int = null
as
if @Test1 is not null and @Test2 is not null
select * from tblTest where Test1 = @Test1 and Test2 = @Test2
else
if @Test1 is not null and @Test2 is null
select * from tblTest where test1 = @Test1
else
if @Test1 is null and @Test2 is not null
select * from tblTest where test2 = @Test2
else
if @Test2 is null and @Test1 is null
select * from tblTest

More params -> more copy-pasting :). Use with care then.

Well. You can also use dynamic SQL to build the query and add only where conditions for params with not null values. But you should be aware on sql injection and query recompilation on every use. As an ultimate solution you can generate the procedure code to apply if else logic automatically.

Thx...working on this now...I'll let you know; I have quite a few fields so this may take some time.

wait  .. I have 25 parameters in the search;

Pls look at this small amount of code:

ALTER PROCEDURE [dbo].[usp_Search1]

@Program varchar (50) = Test,
@Project varchar (255) = tt,
@Center varchar (50) = ttt,
@Facility_Name varchar (255) = NULL

AS
BEGIN

      SET NOCOUNT ON;

Select p.Program, p.Project, p.Center, f.Facility_Name from tbl_Program p join tbl_Facility f on p.MainId = f.MainId
where
@Program = p.Program and
@Project = p.Project and
@Center = p.Center and
@Facility_Name is not null or @Facility_Name = f.Facility_Name

end

I defaulted the Facility_Name para to NULL and then the code bombs and I receive no results; is there another way..I should be receiving all data in the Facility_Name field

whooops hold on Brian...saw your input after I submitted last statement

thx

OK...please look at this code:  I have values for the first 3 para; none for the last 2.  I should be receiving all data for the first 3.  What am I missing?  Thx


ALTER PROCEDURE [dbo].[usp_Search1]

@Program varchar (50) = TTT,
@Project varchar (255) = TT,
@Center varchar (50) = T,
@Facility_Name varchar (255)= NULL,
@BuildNos varchar (255) = NULL

AS
BEGIN

      SET NOCOUNT ON;

Select p.Program, p.Project, p.Center, f.Facility_Name, f.Building_Nos as 'Building Numbers'
from tbl_Program p join tbl_Facility f on p.MainId = f.MainId
where
(@Program is null or p.Program = @Program)
and (@Project is null or p.Project = @Program)
and (@Center is null or p.Center = @Center)
and (@Facility_Name is null or f.Facility_Name = @Facility_Name)
and (@BuildNos is null or f.Building_Nos = @BuildNos)



end