Ad:

MS SQL Discussion Thread
Unsolved
Views: 37748

Apr 20th, 2006

In-line If-Else Block?

Expand Post »

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]

Similar Threads

End if statements in Visual Basic 4 / 5 / 6
Code line numbers - causing paste problems in DaniWeb Community Feedback
Multiple pattern matches inside a BLOCK in Perl
RUNDLL error C:\WINDOWS\DOWNLO~1\CONFLICT.1\ymmapi.dll in Viruses, Spyware and other Nasties
how can i give uniform line spacing for <fo:block> in RSS, Web Services and SOAP

versatilewt

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2006

Permalink

Apr 20th, 2006

Re: In-line If-Else Block?

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

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:

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

Apr 20th, 2006

Re: In-line If-Else Block?

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

versatilewt

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2006

Permalink

Apr 20th, 2006

Re: In-line If-Else Block?

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

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

Permalink

May 10th, 2006

Re: In-line If-Else Block?

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

eralper

Reputation Points: 10

Solved Threads: 2

Newbie Poster

Offline

9 posts
since Aug 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL 2000 Service Pack 3a Installation Problems

Next Thread in MS SQL Forum Timeline: How can I select records randomly from MS SQL Server Table?

DaniWeb Message

Cancel Changes

User Name
Password