SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sp error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ann
Posting Yak Master

220 Posts

Posted - 04/20/2009 :  18:09:49  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 04/20/2009 :  18:37:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/20/2009 :  18:46:51  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 04/20/2009 :  18:49:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/20/2009 :  18:56:21  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 04/20/2009 :  18:59:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/20/2009 :  19:02:53  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 04/20/2009 :  19:03:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000