Function Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Function Question

Author

Topic

gj0519
Starting Member

13 Posts

Posted - 2009-04-16 : 13:43:06

I am trying to create a function and I get an error that states I need a Return Statement. The problem is I don't know where it goes. When I add it in I get other errors. Can someone tell me where my error is?
Thanks,
GJ

Create Function fnGPA()
Returns Decimal(8,2)
Begin
    Declare @GPA Decimal(8,2)

Set @GPA = (Select
Sum((Case When [111_Flag] is null or [111_Flag] = '' Then
   (Case [111_Letter]
        When 'A' Then 4
        When 'B' Then 3
        When 'C' Then 2
        When 'D' Then 1
        When 'F' Then 0 End)Else 
    (Case [111_Flag]
        When 'A' Then 4
        When 'B' Then 3
        When 'C' Then 2
        When 'D' Then 1
        When 'F' Then 0 End)End)) as GPA
From Grade2s
Where [111_Status] = 'A'
and (([111_Letter] is not null and [111_Letter] <> '') or ([111_Flag] is not null and [111_Flag] <> ''))
group by studentid)

End
Go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 13:49:07

[code]
Create Function fnGPA()
Returns Decimal(8,2)
AS
Begin
Declare @GPA Decimal(8,2)

Set @GPA = (Select
Sum((Case When [111_Flag] is null or [111_Flag] = ''
Then
(Case [111_Letter]
When 'A' Then 4
When 'B' Then 3
When 'C' Then 2
When 'D' Then 1
When 'F' Then 0
End)
Else
(Case [111_Flag]
When 'A' Then 4
When 'B' Then 3
When 'C' Then 2
When 'D' Then 1
When 'F' Then 0
End)
End)
) as GPA
From Grade2s
Where [111_Status] = 'A'
and (([111_Letter] is not null and [111_Letter] <> '') or ([111_Flag] is not null and [111_Flag] <> ''))
group by studentid)
return @GPA
End
Go
[/code]

gj0519
Starting Member

13 Posts

Posted - 2009-04-16 : 14:01:13

Thanks,

I get an error when I try to call my function in a select statement.?
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Select Studentid,dbo.fnGPA() 
from Grade2s
where studentid = '1011'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:20:56

i think this would be fine


Create Function fnGPA(@StudID int)
Returns Decimal(8,2)
AS
Begin
    Declare @GPA Decimal(8,2)

Set @GPA = (Select
Sum((Case When [111_Flag] is null or [111_Flag] = '' 
          Then
                (Case [111_Letter]
                 When 'A' Then 4
                 When 'B' Then 3
                 When 'C' Then 2
                 When 'D' Then 1
                 When 'F' Then 0 
                 End)
          Else 
                (Case [111_Flag]
                 When 'A' Then 4
                 When 'B' Then 3
                 When 'C' Then 2
                 When 'D' Then 1
                 When 'F' Then 0 
                 End)
          End)
           ) as GPA
From Grade2s
Where [111_Status] = 'A'
and (([111_Letter] is not null and [111_Letter] <> '') or ([111_Flag] is not null and [111_Flag] <> ''))
and studentid=@StudID)
return @GPA
End
Go
@StudID is id of student for which you want GPA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources