•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 392,001 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,177 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 9109 | Replies: 4
 |
•
•
Join Date: Apr 2006
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
In-line If-Else Block? 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]
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
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
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):
Example B get just one department or all departments:
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
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: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
Location: Dallas, TX
Posts: 481
Reputation:
Rep Power: 4
Solved Threads: 19
Posting Pro in Training
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
•
•
Join Date: Aug 2005
Posts: 6
Reputation:
Rep Power: 0
Solved Threads: 2
Newbie Poster
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
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- End if statements (Visual Basic 4 / 5 / 6)
- Code line numbers - causing paste problems (DaniWeb Community Feedback)
- Multiple pattern matches inside a BLOCK (Perl)
- RUNDLL error C:\WINDOWS\DOWNLO~1\CONFLICT.1\ymmapi.dll (Viruses, Spyware and other Nasties)
- how can i give uniform line spacing for <fo:block> (RSS, Web Services and SOAP)
- Invision Furl problem (PHP)
- AnsiString Template Data Return Problem Builder 6 (C++)
Other Threads in the MS SQL Forum
- Previous Thread: SQL 2000 Service Pack 3a Installation Problems
- Next Thread: My zipcode query is not returning the correct results
