Best way to write SQL with multiple nested IF/Else

Transact-SQL
1

Hello SQL Experts,
Looking for the best way to implement this scenario in a store Procedure where I am not using Dynamic SQL.
The way it is now the SELECT statement is been repeated in each branch of the IF statement. what is the best way
to write a single SELECT with the logic to return the records beas on the user selection.

I am really trying to avoid repeating the select statement in all the branches of the IF statements

The result is mainly base on whether they check the @unposted check box and / or choose an Account mask in their search..

Any and all Ideas will be greatly appreciated.. Thank you..

@Year
@EmpNo
@unposted
@Acct_Mask

IF @unposted = 0 AND @Acct_Mask =''
Select * From Table1

   WHERE 
     Year = @Year
AND  EmpNo = @EmpNo
AND  Process = 0

IF @unposted = 1 AND @Acct_Mask =''
Select * From Table1

WHERE 
     Year = @Year
AND  EmpNo = @EmpNo
AND  (Process = 0 OR Process = 1)

IF @unposted = 0 AND @Acct_Mask !=''
Select * From Table1

WHERE 
     Year = @Year
AND  EmpNo = @EmpNo
AND  Process = 0
AND  AcctNumber LIKE @Acct_Mask

IF @unposted = 1 AND @Acct_Mask !=''
Select * From Table1
WHERE
Year = @Year
AND EmpNo = @EmpNo
AND (Process = 0 OR Process = 1)
AND AcctNumber LIKE @Acct_Mask

I have an interface where the client select each parameter from drop down box, @year and @EmpNo cannot be empty they are always assign a default value
from the application when user the login.

The @unposted parameter come from a check box on the interface that will return 1 if check, and all records where Process = 0 OR Process = 1 shoule be
selected, else only records where process = 0 is selected

And same goes for the @Acct_Mask, If @Acct_Mask is not blank then all records matching (AcctNumber LIKE @Acct_Mask is selected)

2

Something like this:

Select * 
From Table1
WHERE 
     Year = @Year
AND  EmpNo = @EmpNo
AND  (Process = 0 OR (@unposted = 1 AND Process = 1))
AND  (ISNULL(@Acct_Mask, '') = '' OR AcctNumber LIKE @Acct_Mask)
3

Thank you so much for your reply, after rewriting the SP and running it and getting incorrect result I realize that I made an error in the original post. What I really need is when @unposted = 0 return all records where Process = 1, And When @unposted = 1 then return all records where Process = 0 OR Process = 1

I tried AND (Process = 0 OR (@unposted = 1 AND Process = 1)) but that's not giving me the correct records either.. Thanks

IF @unposted = 0 AND @Acct_Mask =''
Select * From Table1

   WHERE 
     Year = @Year
AND  EmpNo = @EmpNo
AND  Process = 1         **********  This should have been Process = 1 not Process = 0
4

...
AND (Process = 1 OR (@unposted = 1 AND Process = 0))
...

5

Thank you, really appreciate your time / help..