The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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
  declare @fhours decimal(5,2);
  set @fhours = 0;
(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(( 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(( 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

asked 21 Jul '16, 13:08

rsnyder's gravatar image

accept rate: 0%

edited 21 Jul '16, 13:24

Mark%20Culp's gravatar image

Mark Culp

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

)  as total
into @fhours
from dba.classes t1
where t1.studnum = @studnum 
    and t1.repeatterm is null

permanent link

answered 21 Jul '16, 13:28

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

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!


(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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 21 Jul '16, 13:08

question was seen: 507 times

last updated: 21 Jul '16, 13:59