Instead of

Union

If(@NPI is not null or @TIN IS Not null)

Begin

You'll need to remove the If condition as well as the BEGIN ... END.

Add the following line to the WHERE Condition of each of the threee union statements you want to exclude:

AND ISNULL(@NPI,@TIN) IS NOT NULL

e.g. the first "conditional union" would look like

Where Cl.keyvaluechar IN (

Select Distinct claim_num from dw..claim where svc_provider_npi=@NPI or Payto_Provider_NPI=@NPI

)

AND ISNULL(@NPI,@TIN) IS NOT NULL

But the question is: Why do you need to do the UNION in the first place?

You could run each statement individually as a separate INSERT into #temptable.

If you want to exclude duplicates, you could add

AND NOT EXISTS (SELECT 1 FROM #temptable WHERE [add the check you want to perform])

In that scenario the section

If(@NPI is not null or @TIN IS Not null)

Begin

is valid and then UNION needs to be removed and separa Insert into #temptable need to be added.