Suggestions for SP - 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

General SQL Server Forums

New to SQL Server Programming

Suggestions for SP

Author

Topic

yanaho
Starting Member

20 Posts

Posted - 2010-06-11 : 10:15:28

Hi there!
I need some help designing a sp. I am trying to use a table with bit values to determine what departments are returned in a query. If anyone has any suggestions on how to accomplish this, or a better way to accomplish my goal, that would be great.

This is the permissions table (shortened for simplicity)

CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserAccountName] [varchar](50) NULL,
[Department1] [bit] NULL,
[Department2] [bit] NULL,
[Department3] [bit] NULL)

This is the table with the departments.
CREATE TABLE [dbo].[Departments](
[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NULL)

Each department (bit) field in Users represents whether that department is pulled in a query.
So the stored procedure would take UserAccountName as input, and return the departments that the user is allowed to view.
Not sure if I've described this well, but I appreciate anyone taking the time to have a look.

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-11 : 11:10:54

Changing your table structure would be the best way. This example should give you an idea of what to do. I don't know what the data in departments looks like, but you could probably manipulate it to make the query work.

Jim

declare @table table (userName varchar(4),col1 bit,col2 bit,col3 bit)
insert into @table
select 'aaa',1,0,1 union all
select 'bbb',1,1,1 union all
select 'ccc',0,0,0 union all
select 'ddd',0,1,0
select b.username,a.* from
(

select username,[id] = case when col1 = 1 THEN 2 else 0 END + case when col2 = 1 THEN 4 else 0 END + case when col2 = 1 THEN 8 else 0 END

from @table

) b
cross join
(select 2 as id ,'dept1' as dept union
select 4,'dept2' union
select 8 ,'dept3'
) a

where a.id & b.id > 0

order by 1

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-11 : 11:20:04

declare @table table (userName varchar(4),dept1 bit,dept2 bit,dept3 bit)
insert into @table
select 'aaa',1,0,1 union all
select 'bbb',1,1,1 union all
select 'ccc',0,0,0 union all
select 'ddd',0,1,0

select username,dept,depts
from

(select username,dept1,dept2,dept3 from @table)p
UNPIVOT (Depts FOR dept in ( dept1,dept2,dept3))
as unpvt

where depts = 1

jim

Everyday I learn something that somebody else already knew

yanaho
Starting Member

20 Posts

Posted - 2010-06-11 : 12:36:44

Hrmmm, I don't fully understand the UNPIVOT and FOR functions, but I modified the query and it seems to get the desired results. So basically, the Users table's bit column names should be the exact name of the department, and this should work.

Thanks a bunch! I will look into these functions to try to gain insight on what is happening.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-11 : 15:28:38

PIVOT and UNPIVOT are new in SQL 2005. "&" Means a bitwise function

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 03:57:14

For unknown number of departmsnts,refer
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

yanaho
Starting Member

20 Posts

Posted - 2010-06-15 : 16:26:26

Is there any way to only return the Departments? without the other two columns? I need to run a another query 'WHERE Department IN (your query)'

Thanks Again!

quote:
Originally posted by jimf

declare @table table (userName varchar(4),dept1 bit,dept2 bit,dept3 bit)
insert into @table
select 'aaa',1,0,1 union all
select 'bbb',1,1,1 union all
select 'ccc',0,0,0 union all
select 'ddd',0,1,0

select username,dept,depts
from

(select username,dept1,dept2,dept3 from @table)p
UNPIVOT (Depts FOR dept in ( dept1,dept2,dept3))
as unpvt

where depts = 1

jim

Everyday I learn something that somebody else already knew

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-15 : 16:33:35

That would mean it should just return dept1,dept2 and dept3 right? Those are the only departments you have in your table structure.

yanaho
Starting Member

20 Posts

Posted - 2010-06-16 : 09:56:45

It should return department where bit value = true

quote:
Originally posted by vijayisonly

That would mean it should just return dept1,dept2 and dept3 right? Those are the only departments you have in your table structure.

yanaho
Starting Member

20 Posts

Posted - 2010-06-16 : 12:03:59

Actually, nevermind. I have found another way to accomplish my goal. Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources