Complex Conditional Query in Where Clause

In my company, each department has their own statuses for purchase orders. I’m trying to load only the pertinent POs for a specific user’s department. I keep getting errors. It seems like it should be correct, though. I initially attempted to use a Case statement, but it appears that SQL can only do a simple return from a case, so I’m now attempting If statements. Current error is Incorrect Syntax near the keyword IF. It looks right to me. It’s as if the incorrect syntax is that it is in the IN parentheses.

    declare @Dept nvarchar(12);
    set @Dept = 'IT'
    SELECT * 
    FROM TBL_ORDERS 
    WHERE ORD_STATUS IN 
    (
    IF @Dept = 'PURCH'
    BEGIN 
        SELECT distinct * FROM  (VALUES ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4')) AS X(a) 
    END
    
    ELSE 
    IF @Dept = 'ADMIN' 
    BEGIN 
        SELECT distinct * FROM (VALUES ('ADStat1')) AS X(a)
    END
    ELSE 
    IF @Dept = 'IT' 
    BEGIN
    SELECT distinct * FROM  (VALUES ('ADStat1'), ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4'), ('ITStat1'), ('ITStat2')) AS X(a)
    END
    ELSE END
    ) 

Answer

There’s a conceptual problem to address here first, and then we can look at how to actually do what you want.

select is starting a statement here. Statements end with a semicolon. You can’t put statements inside other statements. What you are doing is the same as trying to do something like this in c#:

int i = if (true) 1; else 2;;

You can of course use expressions inside statements:

int i = true ? 1 : 2;

Moreover, select is a declarative statement. You can’t do imperative flow of control inside a declarative language construct. You’re mixing metaphors, as it were. To understand the declarative/imperative distinction see this question and in particular (in my opinion) this answer.

So the first thing to do is wrap your head around the declarative nature of SQL statements like select. Yes, T-SQL also includes imperative constructs like if and while, but you can’t do imperative inside declarative.

You can use conditional expressions (and other expressions) inside a declarative statement:

select name,
       case 
         when name = 'date' then 'this is the date row'
         else 'this is not the date row'
       end
from   sys.types;

In this example the declarative select says what to do with all of the rows returned by the from clause. I don’t write a while loop or a for loop in order to instruct the computer to loop over each row and provide instructions inside the loop. The from returns all the rows, and the select declares what I want to do with each of them. The case expression will be evaluated against every row in sys.types.

OK, so what about your specific question? There’s many ways to write the code. Here is one way that is very similar to your current structure. First I conditionally (imperatively!) populate a temp table with the statuses I want. Then I declaratively use that temp table as my filter:

create table #statuses 
(
   statusname varchar(32)
);

declare @dept nvarchar(12) = 'IT';

if (@dept = 'IT') 
begin
   insert #statuses (statusname) values
   ('ADStat1'), ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4'), ('ITStat1'), ('ITStat2');
end 
else if (@dept  = 'PURCH')
begin
   insert #statuses (statusname) values 
   ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4');
end 
else if (@dept = 'ADMIN')
begin
   insert #statuses (statusname) values
   ('ADStat1');
end

select   *
from     tbl_orders
where    ord_status in (select statusName from #statuses);

Can I do it without the temp table? Sure. Here’s one way:

declare @dept nvarchar(12) = 'IT';

select   *
from     tbl_orders
where    (@dept = 'ADMIN' and ord_status = 'ADStat1')
         or (@dept = 'PURCH' and ord_status in ('PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4'))
         or (@dept = 'IT' and ord_status in ('ADStat1', 'PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4', 'ITStat1', 'ITStat2'));

Here we evaluate a different in depending on the value of @dept. Clearly only one of them actually needs to be evaluated, and the other two don’t really need to be there, depending on which value of @dept is provided. Adding an option (recompile) can be beneficial in cases like this. For more information about option (recompile) look here and here.