CODEWITHSUNDEEP
sql-server-2008
jlodenius
jlodenius

Reputation: 829

incorrect syntax in function microsoft sql-server

Getting incorrect syntax near 'FROM', not sure why.. outside of a function this code works fine (provided I change RETURN to SELECT) and gives me the sum of all credits which is a float. Any idea what the problem is?

CREATE FUNCTION [dbo].[getCredits] (@StudentID nchar(10))
RETURNS float
AS
BEGIN
    RETURN sum(credits)
    FROM kurser c
    INNER JOIN student_reg_kurstillfällen ci
    ON ci.kurser = c.coursecode
    WHERE ci.avslutad_kurs = 1 and StudentID = @StudentID
END

Upvotes: 1

Views: 72

Answers (2)

Christian Hagelid
Christian Hagelid

Reputation: 8355

CREATE FUNCTION [dbo].[getCredits] (@StudentID nchar(10))
RETURNS float
AS
BEGIN
    RETURN
    ( 
    SELECT sum(credits)
    FROM kurser c
    INNER JOIN student_reg_kurstillfällen ci
    ON ci.kurser = c.coursecode
    WHERE ci.avslutad_kurs = 1 and StudentID = @StudentID
    )
END

Upvotes: 1

Kaf
Kaf

Reputation: 33839

You are doing a RETURN sum(credits) FROM which is invalid.

Side note: If StudentId is an integer please change your parameter datatype as int or as appropriate.

CREATE FUNCTION [dbo].[getCredits] (@StudentID nchar(10))
RETURNS float
AS
BEGIN
    DECLARE @RetVal float 

    SELECT @RetVal = sum(credits)
    FROM kurser c
    INNER JOIN student_reg_kurstillfällen ci
    ON ci.kurser = c.coursecode
    WHERE ci.avslutad_kurs = 1 and StudentID = @StudentID

    RETURN @RetVal
END

Upvotes: 1

Related Questions