CODEWITHSUNDEEP
sql-server
Alfred
Alfred

Reputation: 255

Syntax error in my user defined function?

I creating a function and I have a red error line under the BEGIN keyword and I can't figure out what is wrong with this query?

USE PR
GO

CREATE FUNCTION fnInsCosts
    (@NoDependents int)
RETURNS TABLE
BEGIN
    RETURN 
        (SELECT 
             EmpName, 
             SUM(BaseCost) AS TotBaseCost, 
             SUM(SpouseIns) AS TotSpouseCost, 
             SUM(DepIns) AS TotDepCost, 
             SUM(DentalCost) AS TotDentalCost,
             SUM(SUM(BaseCost) + SUM(SpouseIns) + SUM(DepIns) + SUM(DentalCost)) AS TotalInsCost
         FROM 
             vwPayroll
         WHERE 
             Dependants = @NoDependents
         GROUP BY 
             EmpName)
END;

Upvotes: 0

Views: 175

Answers (1)

Rodney Ellis
Rodney Ellis

Reputation: 807

try

CREATE FUNCTION dbo.fnInsCosts
    (@NoDependents int)
RETURNS TABLE
AS
RETURN (
    SELECT EmpName, SUM(BaseCost) AS TotBaseCost, SUM(SpouseIns) AS TotSpouseCost
        , SUM(DepIns) AS TotDepCost, SUM(DentalCost) AS TotDentalCost
        , SUM(SUM(BaseCost) + SUM(SpouseIns) + SUM(DepIns) + SUM(DentalCost)) AS TotalInsCost
    FROM vwPayroll
    WHERE Dependants = @NoDependents
    GROUP BY EmpName
)

Upvotes: 1

Related Questions