-- The basics or starting points usually in my UDR header
<< declare @p1_header varchar(30), @p2_header varchar(30), @p3_header varchar(30), @p4_header varchar(30), @begdate varchar(16), @enddate varchar(16)
select @begdate = convert(char(10),@p1_dtime,101) +” “+convert(char(5),@p1_dtime,108) , @enddate = convert(char(10),@p2_dtime,101) +” “+convert(char(5),@p2_dtime,108)


if (@p1 <> NULL) select @p1_header = upper(rtrim(@p1)) else select @p1_header = "ALL xxx"
if (@p2 <> NULL) select @p2_header = upper(rtrim(@p2)) else select @p2_header = "ALL xxx"
if (@p3 <> NULL) select @p3_header = upper(rtrim(@p3)) else select @p3_header = "ALL xxx"
if (@p4 <> NULL) select @p4_header = upper(rtrim(@p4)) else select @p4_header = "ALL xxx"
>>


-- Stuff in the beginning of my body in the declare
@p1_input varchar(32),
@p2_input varchar(32),
@p3_input varchar(32),
@p4_input varchar(32)


-- After my declares to setup my parameters
if (@p1<>NULL) select @p1_input = "," + upper(rtrim(@p1))+"," else select @p1_input = @p1
if (@p2<>NULL) select @p2_input = "," + upper(rtrim(@p2))+"," else select @p2_input = @p2
if (@p3<>NULL) select @p3_input = "," + upper(rtrim(@p3))+"," else select @p3_input = @p3
if (@p4<>NULL) select @p4_input = "," + upper(rtrim(@p4))+"," else select @p4_input = @p4


-- In my where clauses (which allows multi entity and multiple values separated by a comma
-- Change the variable to match your @p variables…
and ((patindex("%,"+rtrim(a.hosp)+",%",@p1_input) !=0) or (@p1_input=NULL and a.hosp <> @p1_input))
and ((patindex("%,"+rtrim(ai.dept)+",%",@p2_input) !=0) or (@p2_input=NULL and ai.dept <> @p2_input))
and ((patindex("%,"+rtrim(ai.proc_no)+",%",@p3_input) !=0) or (@p3_input=NULL and ai.proc_no <> @p3_input))
and ((patindex("%,"+rtrim(ai.pt_class)+",%",@p4_input) !=0) or (@p4_input=NULL and ai.pt_class <> @p4_input))