Help: Need to combine multiple IF queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help: Need to combine multiple IF queries

Author

Topic

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 08:35:49

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))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 08:42:56

Do you want to have three resultsets combined?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 08:43:07

[code]SELECT * FROM tblTestLog WHERE ProductID = @ProductID or @ProductID = -1
UNION
SELECT * FROM tblTestLog WHERE BuildID = @BuildID or @BuildID = -2
UNION
SELECT * FROM tblTestLog WHERE AnalystStatusID = @StatusID or @StatusID = -3[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 08:57:19

Wow thanks for the fast reply.

I tried Union before and it ran into problems. Plus, per Microsoft...Union "Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables."

If it worked, it would return 3 times the number of records. I am looking for a compounded IF statement. Each IF looks at the same table but criteria in different columns.

Image a web page with drop downs can that be selected for search criteria. The user can select all records or a specific record. The problem I have is when I use more than one drop down for the criteria.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 09:00:40

My mistake. This should do it:

SELECT * FROM tblTestLog 
WHERE (ProductID = @ProductID or @ProductID = -1) 
AND (BuildID = @BuildID or @BuildID = -2) 
AND (AnalystStatusID = @StatusID or @StatusID = -3)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 09:01:45

[code]SELECT *
FROM tblTestLog
WHERE (
ProductID = @ProductID OR @ProductID = -1
)
AND
(
BuildID = @BuildID OR @BuildID = -2
)
AND
(
AnalystStatusID = @StatusID OR @StatusID = -3
)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 09:02:04

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 09:03:44

quote:
Originally posted by scotts

If it worked, it would return 3 times the number of records. I am looking for a compounded IF statement. Each IF looks at the same table but criteria in different columns.

You are referring to UNION ALL, which keeps all records of the union.
UNION discards duplicate values, so Harsh first suggestion will work. Not very efficient but it works.

E 12°55'05.25"
N 56°04'39.16"

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 09:05:12

Thanks everyone. I will try these in and let you know.

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 09:40:36

ok...soooo close now.

Both examples provided the same result. Which is now more of a data problem than a SQL.

Msg 245, Level 16, State 1, Line 19
Conversion failed when converting the nvarchar value '3-13-2008 ' to data type int.

If I drop the column it refers to, then it works.

awesome thank you.

However is there a way to get around the datatype problem? I have 5 criteria that I am trying to combine. 2 of the columns are int and the rest are nvarchar types.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 09:42:29

You need to use cast/convert to convert the int datatype to nvarchar

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 09:47:57

Why would you cast/convert a DATE?

Always use proper datatype to store data.

You can easy sort and compare datetime values.
Now you want to do two bad things. Convert a VARCHAR datetime value to INT.

CAST(CONVERT(CHAR(10), CONVERT(DATETIME, YourCol, 101), 112) AS INT)

E 12°55'05.25"
N 56°04'39.16"

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 09:50:22

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

cool trying it now...

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 10:22:13

quote:
Originally posted by Peso

Why would you cast/convert a DATE?

Always use proper datatype to store data.

You can easy sort and compare datetime values.
Now you want to do two bad things. Convert a VARCHAR datetime value to INT.

CAST(CONVERT(CHAR(10), CONVERT(DATETIME, YourCol, 101), 112) AS INT)

E 12°55'05.25"
N 56°04'39.16"

Just FYI... the 3-12-08 or w/e the data is in the field will likely change to something like a version number. ie. 1.0.01 Alpha. It is not a datetime.

I read through Microsoft's examples and they are not helping me. I know my syntax is incorrect...

SELECT * FROM tblTestLog
WHERE (ProductId = @ProductId OR @ProductId = -1)
AND (cast(BuildID = @BuildID OR @BuildID = -2), nvarchar)
AND (AnalystStatusId = @StatusId OR @StatusId = -3)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 10:26:57

quote:
Originally posted by scotts

quote:
Originally posted by Peso

Why would you cast/convert a DATE?

Always use proper datatype to store data.

You can easy sort and compare datetime values.
Now you want to do two bad things. Convert a VARCHAR datetime value to INT.

CAST(CONVERT(CHAR(10), CONVERT(DATETIME, YourCol, 101), 112) AS INT)

E 12°55'05.25"
N 56°04'39.16"

Just FYI... the 3-12-08 or w/e the data is in the field will likely change to something like a version number. ie. 1.0.01 Alpha. It is not a datetime.

I read through Microsoft's examples and they are not helping me. I know my syntax is incorrect...

SELECT * FROM tblTestLog
WHERE (ProductId = @ProductId OR @ProductId = -1)
AND (cast(BuildID = @BuildID OR @BuildID = -2), nvarchar)
AND (AnalystStatusId = @StatusId OR @StatusId = -3)

Change like this:-

SELECT * FROM tblTestLog
WHERE (ProductId = @ProductId OR @ProductId = -1)
AND (cast(BuildID as nvarchar(length)) = @BuildID OR @BuildID = -2)
AND (AnalystStatusId = @StatusId OR @StatusId = -3)

Also specify length of your character data inside CAST

scotts
Starting Member

7 Posts

Posted - 2008-04-14 : 10:48:50

ok i see one of the problems...
on my sql query test i didnt change my declare statement. here is the new query. next I am going to make it work on the web page level. You guys are great. Thanks again.


USE Database

DECLARE @ProductID int;
DECLARE @BuildID nvarchar(MAX);
/* ^^ This DECLARE was set to "int" *?
DECLARE @StatusID int;

SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3

SELECT * FROM tblTestLog
WHERE (ProductId = @ProductId OR @ProductId = -1)
AND (BuildID = @BuildID OR @BuildID = -2)
AND (AnalystStatusId = @StatusId OR @StatusId = -3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources