Help: Need to combine multiple IF queries

scotts · April 14th, 2008, 07:19 AM

I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IF\ELSE that would be a huge help. I noted my query below.

/* will remove for aspx page use */
USE Database

/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;

/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3

/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/

IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))

IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))

IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))

planoie · April 14th, 2008, 07:54 PM

First, thank you for providing a clear sample of your code.

It seems you are providing this branching for the purpose of this logic: "If a filter value is specified, use it, otherwise don't filter." How I usually approach this kind of scenario is to declare the parameters as nullable, then put the "if there's a value" test right into a single query like this:

Code:

DECLARE @ProductID int = null;
DECLARE @BuildID int = null;
DECLARE @StatusID int = null;

SELECT * FROM tblTestLog
WHERE
   ProductID = ISNULL(@ProductID, ProductID)
   AND BuildID = ISNULL(@BuildID, BuildID)
   AND AnalystStatusID = ISNULL(@StatusID, AnalystStatusID)

Using this logic, the right side of the test for any parameter passed as null or not passed at all) will revert to the row's field value and thus will always be true. When all are null all rows will be returned.

You then just need to move the logic of what parameters to pass to the query into the application code instead of the database. This gives you the added benefit of not needing any "magic numbers" in the query/proc.

I have used this technique extensively for the purpose of having a single stored procedure that can be used for optionally filterable record retrieval and have not found there to be performance problems. Arguably, there is some overhead in the execution plan for the where clause testing of each row, but I haven't noticed it having an impact. It certainly makes for considerably less procs.

-Peter
peterlanoie.blog