case statement 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 2000 Forums

Transact-SQL (2000)

case statement problem

Author

Topic

JasonJanofsky
Starting Member

5 Posts

Posted - 2007-01-12 : 15:02:21

My problem is this:

Select ItemID from Table where StaffID in (
case when @StaffID in
(select staffID from StaffRole where Role = 'admin')
then
(select staffID from staffRole where Role = 'PowerUser')
end)
)

Every time I try a query like this I get a subquery returned more than one value error. What I am saying here is:

I want every item from a table where:
if the staffID exists in the admin roles, show power users

Any ideas?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 15:41:14

Break it up into two parts.

First, you need a list of staff members in the two roles you are allowing:


select distinct StaffID from StaffRole where Role in ('admin','PowerUser')

Type it in, try it in QA, test it out, look at the results, make sure it looks good, troubleshoot and optimize it before moving on.

Once you have that, you simply join to that from your other "Table":


select ItemID
from Table
inner join
  ( --the above sql goes here-- ) r on Table.StaffID = r.StaffID

The INNER JOIN will limit the results in the table to those where the StaffID has a match in the SQL that you just wrote, returning the valid staffID's that you want.

Always break your SQL into smaller parts, test and debug and optimize each part, and then put it all together at the end.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 15:51:32

What to do when staffid is not in admin?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-12 : 15:53:44

[code]
Select ItemID
from Table a
where EXISTS (select * from StaffRole b
where Role = 'admin' and a.StaffID = b.StaffID)
AND EXISTS (select * from StaffRole c
where Role = 'PowerUser' and a.StaffID = c.StaffID)

[/code]

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JasonJanofsky
Starting Member

5 Posts

Posted - 2007-01-12 : 17:14:01

Thank you everyone for all your quick replies.

The problem I am having is related to the case statement I believe. Let me rephrase the original question.

I have a table to records that have a staffID associated with them.

When the current logged in user wants to view records, I would like them to see only thier own record, unless the logged in user is an admin, in which case they should see all records.

My attempt to shrink down my actual query text for simplicity apparently did not leave the needed context in.

So, in the query I listed here, the case statement:

case when @staffID 'this is the currently logged in users ID

in (select staffID from staffRole where Role = 'admin') 'in the actual query an integer is used here instead of the word admin. This line checks to see if the @StaffID (currently logged user) is an admin)

then 'I want my then here to run the following line against the "in" statement in the parent query.

(select staffID from staff) 'to make this even simpler, I really just want to get all the records here..

end

So basically, the problem I encounter is that after the "then" statement, a list ('x', 'y') returns a "subquery returns more than 1 value" error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-13 : 05:17:15

IF EXISTS (SELECT NULL FROM StaffRole WHERE Role = 'Admin' AND StaffID = @StaffID)
SELECT * FROM YourTable
ELSE
SELECT * FROM YourTable WHERE StaffID = @StaffID

Peter Larsson
Helsingborg, Sweden

JasonJanofsky
Starting Member

5 Posts

Posted - 2007-01-13 : 12:15:46

Quote________________________________________________________
IF EXISTS (SELECT NULL FROM StaffRole WHERE Role = 'Admin' AND StaffID = @StaffID)
SELECT * FROM YourTable
ELSE
SELECT * FROM YourTable WHERE StaffID = @StaffID

Peter Larsson
Helsingborg, Sweden
____________________________________________________________

Yeah, that will work. Thanks for the solution. I actually feel like an ediot now. I had forgotten that the first thing I tried for this was an if statement at the beginning of the proc, but I didn't want to upkeep the nearly identical queries. But.. yeah, this will work great, thanks for getting me back on track Peter.

-Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources