Complex Conditional Query in Where Clause

Question

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
    )

Why would this not be in a table? Coding these attributes just increase the maintenance and number of touchpoints — John Cappelletti, Aug 03 '22 at 15:41
@JohnCappelletti The values are in a table, but depending on what department someone is in, I want to pull all POs that have the very specific values. — rockclimber510, Aug 03 '22 at 16:21
@BarryCarter and lptr, My select statements are kind of remnants of me attempting to cheat the case statement. I was trying to find a way to return multiple values. If there's a way to just say, if-then populate my IN parenthesis with these specific values, that would be great. — rockclimber510, Aug 03 '22 at 16:24
As a side note, I was able to essentially get the values I want from the select statement, however, the front end of the system I'm using requires me to create a filter, and only gives me the option to create a where clause. — rockclimber510, Aug 03 '22 at 16:26
IF and BEGIN END are statements. You cannot use statements in a SELECT or INSERT or UPDATE or DELETE statement. You can only use expressions. CASE is an expression, i.e., it yields a value. — Olivier Jacot-Descombes, Aug 03 '22 at 16:26

allmhuran · Accepted Answer · 2022-08-03T16:45:48.630

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.

Thank you, @allmhuran! Now I feel dumb. Somehow, I overlooked the or statement and went way more complex than I needed. Very much appreciated! — rockclimber510, Aug 03 '22 at 16:30

score 1 · Answer 2 · answered Aug 03 '22 at 16:22

If for a reason storing those status/department data in tables is not possible you can use union

declare @Dept nvarchar(12);
set @Dept = 'IT'
SELECT * 
FROM TBL_ORDERS 
WHERE ORD_STATUS IN 
( 
    SELECT distinct * 
    FROM  (VALUES ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4')) AS X(a)
    WHERE @Dept ='PURCH'

    union all

    SELECT distinct * 
    FROM (VALUES ('ADStat1')) AS X(a)
    WHERE @Dept ='ADMIN'

    union all

    SELECT distinct * 
    FROM  (VALUES ('ADStat1'), ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4'), ('ITStat1'), ('ITStat2')) AS X(a)
    WHERE @Dept ='IT'
)

Complex Conditional Query in Where Clause

2 Answers2