Another way to handle this is with a simple case statement in the WHERE clause. We use this alot as most of our sp's run with multiple, non-required paramenters. We have procs with 15-20 parms and it works fine.

Example proc:

create test_proc

@parm1 int=0,

@parm2 varchar(10)=null,

@parm3 varchar(20)=null

as

select

field1,

field2,

field99

from

tablex

where

1 = case

when @parm1 = field1 then 1

when @parm1 is null then 1

else 0

end

and 1 = case

when @parm2 = field2 then 1

when @parm2 is null then 1

else 0

end

and 1 = case

when @parm3 = field99 then 1

when @parm3 is null then 1

else 0

end