| Author |
 Topic  |
|
|
ann
Posting Yak Master
215 Posts |
 Posted - 04/20/2009 : 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
USA
35007 Posts |
|
|
ann
Posting Yak Master
215 Posts |
Posted - 04/20/2009 : 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 |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
ann
Posting Yak Master
215 Posts |
Posted - 04/20/2009 : 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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
ann
Posting Yak Master
215 Posts |
Posted - 04/20/2009 : 19:02:53
|
yes that works.. thanks
sigh... don't know why I made it so much more complicated |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
sp error
