Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 14th, 2008, 07:19 AM
Registered User
 
Join Date: Mar 2008
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help: Need to combine multiple IF queries

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))
 
Old April 14th, 2008, 07:54 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine two queries snufse SQL Server 2005 6 June 24th, 2008 09:42 PM
Combine sql queries snufse SQL Server 2005 16 June 17th, 2008 03:47 PM
Combine multiple records onto one line bcahillane MySQL 0 December 6th, 2006 04:54 PM
combine 2 queries collie SQL Server 2000 2 November 29th, 2004 03:09 PM
Combine queries lryckman Access VBA 2 May 11th, 2004 11:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.