December 7, 2010 at 10:44 pm
Please help me correct the following function which shows the following error when i try to execute it.I'm new to functions..
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
My code is:
create function feediff_rollno1 (@prollno int)
returns int as
begin
declare @diff int
set @diff =(select max(coursefees)-sum(fee.amount)
from feespaid fee,batch
where
substring(cast(fee.rollno as varchar(50)) ,1,2)=substring(cast(batchcode as varchar(50)),1,2) and
rollno = @prollno
group by rollno,batchcode)
return @diff
end
select dbo.feediff_rollno(11001100)
December 7, 2010 at 11:05 pm
This is returning more than one row
select max(coursefees)-sum(fee.amount)
from feespaid fee,batch
where
substring(cast(fee.rollno as varchar(50)) ,1,2)=substring(cast(batchcode as varchar(50)),1,2) and
rollno = @prollno
group by rollno,batchcode
If you're going to use it the way you are, it needs to return one and only one row.
Also, you are aware that scalar user-defined functions are often a performance nightmare?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2010 at 11:09 pm
select max(coursefees)-sum(fee.amount)
from feespaid fee,batch
where
substring(cast(fee.rollno as varchar(50)) ,1,2)=substring(cast(batchcode as varchar(50)),1,2) and
rollno = @prollno
group by rollno,batchcode
the above Query is returning more thatn one row for the @diff variable where it is permited to take only one
December 9, 2010 at 2:11 am
I suppose the real question here is: do you intend to return more than one value from this, in which case you can't use a scalar function, or is the error in the code that returns multiple rows when you only want one?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply