A Seach Proc with optional parameters

[joebickley]

Hi
Im trying to make a proc return a list of data depending on what parameters are sent too it. There are 8 parameters all of which are optional. If non are sent it returns all the records if 2 are sent it uses them etc etc

How can i build a wher clause to reflect this as it can be different each time.

Thanks

Joe

 

[nigelrivett]

where fld1 = coalesce(@prm1,fld1)
and fld2 = coalesce(@prm2,fld2)
and ...

or you can build up the statement in dynamic sql.
declare @sql varchar(8000)

if @prm1 is not null
select @sql = colesce(@sql + ' and','where') + 'fld1 = ''' + @prm1 + ''''
if @prm2 is not null
select @sql = colesce(@sql + ' and','where') + 'fld2 = ''' + @prm2 + ''''
if ...


select @sql = coalesce(@sql,'')
exec (@select + @sql)


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[joebickley]

Perfect !!

thanks a lot for that!