In-line If-Else Block?

[versatilewt]

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.

SELECT *
	FROM tablename
	IF (condition1 = 'a')
		WHERE condition2 = 'a'
	ELSE
		WHERE condition2 = 'b'

[campkev]

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

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:

create proc sp_generic (@deptid int )

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

[versatilewt]

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

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)

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

[campkev]

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

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

[eralper]

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