MS SQL

In-line If-Else Block?

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Apr 2006

Posts: 3

Reputation: versatilewt is an unknown quantity at this point

Solved Threads: 0

versatilewt versatilewt is offline

Offline

Newbie Poster

In-line If-Else Block?

Apr 20th, 2006

Hi,

I'm writing some complex queries on a very poorly designed database. We're running on MS SQL Server2000. I was wondering if its possible to do in-line if-else blocks within a query.. i.e.

[PHP]
SELECT *
FROM tablename
IF (condition1 = 'a')
WHERE condition2 = 'a'
ELSE
WHERE condition2 = 'b'
[/PHP]

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: In-line If-Else Block?

Apr 20th, 2006

yes. There are a couple of different ways depending on how your database is setup and what you are trying to do with your query. Here are a couple of examples. Mess with them and let me know if you need more help.
Example A (get customer or deleted customer):

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE proc sp_generic (@cust int, @type int)
IF @type = 1
 SELECT * FROM customer WHERE customerid = @cust
else
 SELECT * FROM deleted_customers WHERE customerid = @cust
create proc sp_generic (@cust int, @type int)
if @type = 1
 select * from customer where customerid = @cust
else
 Select * from deleted_customers where customerid = @cust

Example B get just one department or all departments:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE proc sp_generic (@deptid int )
 
SELECT * FROM employees WHERE departmentid = case when @dept >0 then @dept else departmentid end
ORDER BY departmentid
create proc sp_generic (@deptid int )

select * from employees where departmentid = case when @dept >0 then @dept else departmentid end
order by departmentid

•

Join Date: Apr 2006

Posts: 3

Reputation: versatilewt is an unknown quantity at this point

Solved Threads: 0

versatilewt versatilewt is offline

Offline

Newbie Poster

Re: In-line If-Else Block?

Apr 20th, 2006

Thanks for the reply. I don't have access to create procedures on the system, though I probably should. I think I over simplified my question a bit. Here's exactly what I'm trying to do. I want to set WHERE conditions optionally, based on a subquery.

The data isn't given explicity, so I have to kind of infer it through the query. I want to set a where condition of e.stafflevelid = m.stafflevelid and project_dept = manager_dept ONLY when then Manager level is, say 4 (which is determined from a subquery).

So, for the query
[php]

WHERE
e.active <> 0
AND
/* THIS IS THE OPTIONAL WHERE CONDITION */
/* IF THE MANAGER'S STAFF LEVEL SATISFIES SUBQUERY*/
(mgr.stafflevelid IN (/* HERE WOULD BE SUBQUERY*/)
/* THEN SET THESE WHERE CONDITIONS*/
e.stafflevelid=m.stafflevelid
p.dept = m.dept)
[/php]

And, I want this condition to run with other WHERE statements

I know it's not pretty, but I'm working with bad structure. Hopefully this isn't too confusing.

Thanks,

Brian

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: In-line If-Else Block?

Apr 20th, 2006

still not sure I understand fully what you want but maybe this will help

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
IF (mgr.stafflevelid IN (/* HERE WOULD BE SUBQUERY*/) 
	SELECT [COLUMNS] FROM [TABLES] WHERE e.active <> 0 AND e.stafflevelid=m.stafflevelid AND p.dept = m.dept) 
else
	SELECT [COLUMNS] FROM [TABLES] WHERE e.active <> 0
if (mgr.stafflevelid IN (/* HERE WOULD BE SUBQUERY*/) 
	select [columns] from [tables] where e.active <> 0 and e.stafflevelid=m.stafflevelid and p.dept = m.dept) 
else
	select [columns] from [tables] where e.active <> 0

•

Join Date: Aug 2005

Posts: 6

Reputation: eralper is an unknown quantity at this point

Solved Threads: 2

eralper

Offline

Newbie Poster

Re: In-line If-Else Block?

May 10th, 2006

Hi,

I hope the below script may help you.

WHERE
CASE
WHEN Code LIKE 'A%' THEN OrderNumber
ELSE 10
END = 20
OR
CASE
WHEN Code LIKE 'E%' THEN OrderNumber
ELSE 10
END = 6

The below checks whether OrderNumber equals to 20 where Code begins with 'A'

CASE
WHEN Code LIKE 'A%' THEN OrderNumber
ELSE 10
END = 20

The second parts checks whether OrderNumber equals to 6 where Code begins with 'E'

Eralper
http://www.kodyaz.com