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 2005 Forums
 Transact-SQL (2005)
 sp error

Author  Topic 

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 18:09:49
I get the following error when I try to run my sp:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression


this is my sp:
-- Add the parameters for the stored procedure here
@rptID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @trgID int

set @trgID =
(select targetID
from tblsections
where reportid = @rptID)

select * from tblUserTargets ut
join tblusers u on u.userid = ut.userid
where ut.targetid in (select targetID from tblUserTargets)

what do I need to change to make this work?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-20 : 18:37:01
The problem is here:

set @trgID =
(select targetID
from tblsections
where reportid = @rptID)

It returns more than one value, hence the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 18:46:51
yes .. I knew that ... but when I ran it in query analyzer, it returned all the correct values?

What I want is all the targetID's in the tblsections to get the results in the second query

By the way, I am using 2000 to test these queries since that is where the data resides - it will be moved to 2005, hopefully in a couple of months
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-20 : 18:49:06
I'm not following you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 18:56:21
Sorry, sometimes it's hard to put in words what you are thinking.

Anyway, maybe this is more clear - I am passing in the param of reportID = 8 in this example:
tblsections
targetID reportID
1 7
2 8
3 8

tblusertargets
userID targetID
1 2
2 1
3 3


tblUsers
userID firstname lastname
1 mickey mouse
2 minnie mouse
3 john doe

results
1 mickey mouse
3 john doe
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-20 : 18:59:02
This is just a simple join:

SELECT u.userID, u.firstname, u.lastname
FROM tblUsers u
JOIN tblusertargets ut
ON u.userID = ut.userID
JOIN tblsections s
ON ut.targetID = s.targetID
WHERE s.reportID = @rptID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

ann
Posting Yak Master

220 Posts

Posted - 2009-04-20 : 19:02:53
yes that works.. thanks

sigh... don't know why I made it so much more complicated
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-20 : 19:03:18
You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
   

- Advertisement -