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

asked 21 Jul '16, 13:08

rsnyder's gravatar image

rsnyder
436121429
accept rate: 0%

edited 21 Jul '16, 13:24

Mark%20Culp's gravatar image

Mark Culp
24.8k10139296


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
permanent link

answered 21 Jul '16, 13:28

Mark%20Culp's gravatar image

Mark Culp
24.8k10139296
accept rate: 41%

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

(21 Jul '16, 13:58) rsnyder

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

(21 Jul '16, 13:59) rsnyder

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

Becky

(21 Jul '16, 13:59) rsnyder
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×20

question asked: 21 Jul '16, 13:08

question was seen: 1,926 times

last updated: 21 Jul '16, 13:59