CODEWITHSUNDEEP
databasesql-server-2008sumsql-function
user3804193
user3804193

Reputation: 113

How to write a SQL Server scalar function to calculate the sum?

I'm having an issue with SQL Server 2008, I want to write a function that returns the sum of a specific column, the code goes like this:

CREATE FUNCTION [dbo].[GetIssuesSum](@i int)
RETURNS int
AS
BEGIN
    DECLARE @IssueSum int

    SELECT SUM (@IssueSum = Quantity) 
    FROM Issue

    RETURN @IssueSum

END

When I try to execute it, SQL Server 2008 throws this error

Msg 102, Level 15, State 1, Procedure GetIssuesSum, Line 15
Incorrect syntax near '='.

Any suggestions? Thanks in advance

Upvotes: 2

Views: 4886

Answers (1)

marc_s
marc_s

Reputation: 755531

You need to use this code:

CREATE FUNCTION [dbo].[GetIssuesSum](@i int)
RETURNS int
AS
BEGIN
    DECLARE @IssueSum int

    SELECT @IssueSum = SUM(Quantity) 
    FROM Issue

    RETURN @IssueSum
END

You need to assign the SUM(Quantity) to your SQL variable - not have the SQL variable inside the SUM() expression

Upvotes: 1

Related Questions