cancel
Showing results for 
Search instead for 
Did you mean: 

Function complaining of result set. SQLA 16

2,505

Wrote this pre-requisite function to determine if a student has a minimum amount of hours to take a certain course. But I must exclude a repeated course in the total. Runs great in ISQL, but when calling the function, it complains "Result set not permitted in 'HoursUpperLevelFinance'" Any ideas what it is tripping on? TIA, Becky (Bradley University)

ALTER FUNCTION "DBA"."HoursUpperLevelFinance"( in @studnum unsigned integer,in @pregrades bit default 0 ) 
returns decimal(5,2)
not deterministic
begin
  declare @fhours decimal(5,2);
  set @fhours = 0;
select 
sum
(
(select top 1 credit into @fhours
from dba.classes t2 
where t2.studnum = t1.studnum and t2.dropdate is null and coalesce(t2.grade,'') <> 'W'
    and((t2.credit is not null and t2.honorpoint is not null) or @pregrades = 1)  
    and t2.course = t1.course
    and t2.course_no = t1.course_no
ORDER BY t2.repeatterm desc) 
)  as total
from dba.classes t1
where t1.studnum = @studnum 
    and t1.dropdate is null and coalesce(t1.grade,'') <> 'W' 
    and((t1.credit is not null and t1.honorpoint is not null) or @pregrades = 1)  
    and t1.course = 'FIN'
    and t1.course_no >= 300
    and t1.repeatterm is null
;
  if @fhours is null then set @fhours = 0 end if;
  return @fhours
end

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

You need an "INTO @fhours" in your select statement... otherwise the result set is returned to the caller. Example:

select 
sum
(
   ...[snip]...
)  as total
into @fhours
from dba.classes t1
where t1.studnum = @studnum 
    ...[snip]...
    and t1.repeatterm is null
;

HTH
0 Kudos

Thank-you for your response. It's there...Do I have it misplaced? "select top 1 credit into @fhours"

0 Kudos

Oh I SEE where you're saying it should be. Will give that a try, thanks!

0 Kudos

Yes, that did it. A misplaced "into"! Thanks!

Becky

Answers (0)