CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012
SBB
SBB

Reputation: 8970

TSQL Conditional Where using Temp Tables

I have a procedure that has takes an XML string of option field data and stores values in temp tables if anything was passed by the user.

For example, if the user selected they wanted to search by Country and Department, those temp tables would contain data where as City would be empty.

What I am trying to do is create a where clause that will use the data in those temp tables as its logic.

    INSERT  INTO @users(QID)
    SELECT  QID
    FROM    Employees AS e
    WHERE   e.AreaID IN ( SELECT AreaID FROM @areas )
    AND     e.SiloID IN ( SELECT SiloID FROM @silos);

In this situation however, if @silos was empty, I don't want that to be part of the logic and should include data where the e.SiloID is anything.

Examples:

@Areas

1, 5, 7

@Silos

NULL

Query would be:

SELECT QID 
FROM Employees 
WHERE e.AreaID IN ( SELECT AreaID FROM @areas )
AND   e.SiloID = <anything here, we ignore this logic because the temp table was empty>;

Upvotes: 0

Views: 58

Answers (2)

Tyler Roper
Tyler Roper

Reputation: 21672

Each of your where conditions should check if the value is in the temporary table or the temporary table is empty. This would create a "conditional filter" that only narrows down results if the temporary table has records in it.

INSERT  INTO @users(QID)
SELECT  QID
FROM    Employees AS e
WHERE   ( 
            -- Areas
            NOT EXISTS (SELECT AreaID FROM @areas) 
            OR e.AreaID IN ( SELECT AreaID FROM @areas )
        ) 
        AND ( 
            -- Silos
            NOT EXISTS (SELECT SiloID FROM @silos) 
            OR e.SiloID IN ( SELECT SiloID FROM @silos )
        );

Continue this pattern for remaining conditions.

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

You can use a rowcount variable as others have mentioned in comments, or you can do it inline:

AND     (
  e.SiloID IN ( SELECT SiloID FROM @silos)
  OR NOT EXISTS(SELECT SiloID FROM @silos)
);

Upvotes: 0

Related Questions