We all know the user .... the one who wants it all.  well... one time I met one that wanted a crystal report that would prompt them for 12 different parms.  She wanted to be able to narrow the amount of records that came back just by filling more fields.  Heck, she didn't even want to fill out the whole value, but maybe just part of whichever ones she wanted to try.  Sounds like a lot of "IF' statements and wildcards eh?

For example, lets say that you had only 5 different parms.  The user would fill out something in parm 4 but leave all the other parms blank.  That would make the where statement give only those items where the field for parm 4 matched what she input.  but lets say she also filled out parm 3.  then the where statement would look like below if we didnt have to deal with those 3 other parms.

select * from mytable
where parm4field = @parm4 and parm3field = @parm3

Ok. now what do we do with the other parms?  let me show you a neat T-SQL trick

create proc myproc
@parm1 as varchar(255)  = 'all',
@parm2 as varchar(255) = 'all', @parm3 as varchar(255) = 'all',@parm4 as varchar(255)= 'all',@parm5 as varchar(255) = 'all'
 select *
from my table
where
(@parm1 = 'all'
or parm1field = '%' + @parm1 + '%') 
and
(@parm2 = 'all'
or parm2field = '%' + @parm2 + '%') 
and
(@parm3 = 'all'
or parm3field = '%' + @parm3 + '%') 
and
(@parm4 = 'all'
or parm4field = '%' + @parm4 + '%') 
and
(@parm5 = 'all'
or parm5field = '%' + @parm5 + '%')  

This says that either the parm is the default of I provided something else for it.   This is wildcarded so the user can enter evern part of a value...slick eh?

MikeB
MCDBA