Syntax error in my user defined function?

Question

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;

For one thing, stored functions should always have the schema qualifier - e.g. CREATE FUNCTION dbo.fnInsCosts - including when you're calling them — marc_s
– marc_s, Commented Apr 17, 2020 at 4:38
Is this a new practice? My textbook which is sql server 2016 doesn't show it written this way? — Alfred
– Alfred, Commented Apr 17, 2020 at 4:40
No - well established, long time best practice - nothing new — marc_s
– marc_s, Commented Apr 17, 2020 at 4:42

Dale K · Accepted Answer · 2020-04-17 06:28:11Z

1

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
)

edited Apr 17, 2020 at 6:28

Dale K

28.2k15 gold badges59 silver badges85 bronze badges

answered Apr 17, 2020 at 4:36

Rodney Ellis

7933 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alfred Over a year ago

This seemed to work? Why would it though I've written a few of these functions like my OP and no issues?

Rodney Ellis Over a year ago

did you try it?

Alfred Over a year ago

Msg 130, Level 15, State 1, Procedure fnInsCosts, Line 8 [Batch Start Line 9] Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Msg 208, Level 16, State 1, Line 23 Invalid object name 'dbo.fnInsCosts'.

Alfred Over a year ago

Ok sorry you are right thank you!. You mind helping with this one though lol, I'm pretty confused, and don't want to waste all my questions

Rodney Ellis Over a year ago

it's to do with your SUM(SUM()) . not sure why you're doing a SUM of a SUM. try getting rid of that and see how you go.

|

Collectives™ on Stack Overflow

Syntax error in my user defined function?

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related