this returned more than 1 value

Question

I run the following query:

create function passs (@studentid nvarchar(50),@courseid nvarchar(50))
returns char
as
begin
declare @k char
if((select Grades.grade from Grades where Grades.Courses_id=@courseid AND 
Students_id=@studentid) >60)
set @k='pass'
else
set @k='no pass'
return @k
end

when i run this :

select Grades.Courses_id,dbo.pass(Grades.Students_id,Grades.Courses_id) 
,Grades.Students_id,Grades.grade from Grades

i got this

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I think the error is pretty obvious. You need to explain what to do. Also tag the question with the database you are using. — Gordon Linoff, May 22 '18 at 13:47
Possible duplicate of [Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression](https://stackoverflow.com/questions/16053907/subquery-returned-more-than-1-value-this-is-not-permitted-when-the-subquery-foll) — EzLo, May 22 '18 at 13:49
You're getting more than one grade for the student/course combination. What do you want to do if they got `50` in one row and `70` in another row? Take their lowest grade, highest grade, average grade, something else? — MatBailie, May 22 '18 at 14:00
You could use [`exists`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017) to determine if the student has _any_ grade for the class that exceeds `60`. — HABO, May 22 '18 at 16:49

score 0 · Answer 1 · edited May 22 '18 at 13:53

You need to specify what to do when a student has more than one grade for the same class -- that is the error you are getting.

Here is one possibility:

create function passs (@studentid nvarchar(50), @courseid nvarchar(50))
    returns varchar(255)
as
begin
    declare @k varchar(255);

    select @k = (case when min(g.grade) > 60 then 'pass' else 'no pass' end)
    from grades g
    where g.Courses_id = @courseid 
      AND g.Students_id = @studentid

    return @k;
end;

Notes:

Declared the length of the return string -- your values have more than one character.
Declared the length of @k.
Combined the logic into a single query.
Use min() to reduce the query return to a single row to avoid your specific problem.

I think should be `MAX(g.grade)`, student wont take the same course again if already pass it the first time — Juan Carlos Oropeza, May 22 '18 at 13:50

this returned more than 1 value

1 Answers1