IF - THEN Problem - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

IF - THEN Problem

Author

Topic

risho
Starting Member

12 Posts

Posted - 2010-02-02 : 12:53:56

Greetings,

I have a sproc that displays data from several tables and 2 different databases. Basically, one db holds info about assets including user id (ident) as foreign key from the table in the 2nd db. In the 2nd db I have another table that contains users of different category. Both category of users represent individuals who have assets assigned to them like PC, monitors, etc. One category of users from People table are real people and this table is linked to the active directory. The other category of users are virtual people who have such asset assigned to them that are shared. These would include assets such as network printers, plotters, fax machines, etc. These virtual people are located in a table called people_virtual. People_virtual is not linked to the AD.

In this sproc I want to be able to make a decision based on the value of the ident (foreign key) in the assets table when the ident's value is less then (say) 3100 which is the starting id from people table. Below is a shortened version of the sproc. Notice where the nested select statements are containing a fetch from a Queue database.

select Assets.Ident,
Assets.Serial_Number,Assets.Category_Ident, Categories.Description as Category,
Assets.Purchase_Order,
Manufacturers.Description as Manufacturer,
Assets.Software_Ident,
Software_Titles.Software_Title,
Assets.Account_Ident,
Accounts.Description as Account,
Assets.User_Ident,

-- Here is where I need to put in the if-then-else or when-then statement such that:
WHEN Assets.User_Ident > 3100 THEN
(select Queue.dbo.people.user_name from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident) as 'User Name',
(select Queue.dbo.people.first_name from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident) as 'First Name',
(select Queue.dbo.people.last_name from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident) as 'Last Name',
(select queue.dbo.rank.description from Queue.dbo.rank where Queue.dbo.rank.ident = (select Queue.dbo.people.rank from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident)) as 'Rank',
(select Queue.dbo.people.phone from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident) as 'Phone',
(select Queue.dbo.people.smtp_address from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident) as 'Email',
(select queue.dbo.organization.description from Queue.dbo.organization where Queue.dbo.organization.ident = (select Queue.dbo.people.organization_ident from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident)) as 'Organization',
(select queue.dbo.location.description from Queue.dbo.location where Queue.dbo.location.ident = (select Queue.dbo.people.location_ident from Queue.dbo.people where Queue.dbo.people.ident = Assets.User_Ident)) as 'Location',

-- ELSE
(select Queue.dbo.people_virtual.name from Queue.dbo.people_virtual where Queue.dbo.people_virtual.ident = Assets.User_Ident) as 'User Name'

Assets.Disposition_Ident,
Disposition.Description as Disposition

FROM Assets

left outer join Categories on Assets.Category_Ident = Categories.Ident
left outer join Manufacturers on Assets.Manufacturer_Ident = Manufacturers.Ident
left outer join Software_Titles on Assets.Software_Ident = Software_Titles.Ident
left outer join Accounts on Assets.Account_Ident = Accounts.Ident
left outer join Disposition on Assets.Disposition_Ident = Disposition.Ident

WHERE ((Accounts.Description like '%' @account '%') or (Assets.Account_Ident like '%' @account '%'))

order by Serial_Number

Thank you for reading my post and looking forward to reading you suggestions.

EJM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-02 : 13:33:48

You can not show dynamic number of columns in a single query based on certain condition (unless we are talking about dynamic sql, shudder!)
Another thing is instead of including so many individual SELECT statement, you can combine them as a part of JOINS.

For example,


Select ...
p.user_name as [User Name], p.first_name as [First Name],
p.last_name as [Last Name], r. description, ...
from Assets A JOIN Queue.dbo.people p
on p.ident = A.User_Ident
Join Queue.dbo.rank r on r.ident = p.ident
...
Where A.User_Ident > 3100
and ...
union all
Select ...
p.user_name as [User Name], p.first_name as [First Name],
p.last_name as [Last Name], r. description, ...
from Assets A JOIN Queue.dbo.people p
on p.ident = A.User_Ident
Join Queue.dbo.rank r on r.ident = p.ident
...
Where A.User_Ident <= 3100
and ...

I hope you are getting the point.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources