CODEWITHSUNDEEP
sql-server
pjlamb12
pjlamb12

Reputation: 2422

SQL Adding Function

I'm trying to write a function that adds values that are selected from two different tables. I need the SUM of one column for a specific id in table 1, added to the SUM of a column for a specific id in table 2.

CREATE FUNCTION dbo.getSum(@id varchar(9)) --Our IDs are strings of length 9
RETURNS integer --I've also tried decimal(x,x) and int
AS
BEGIN
DECLARE @total as integer; -- same here with decimal and int
SELECT @total = 
(
    (select SUM(Amount)
    from table1
    where id = @id)
    +
    (select SUM(amount)
    from table2
    where id = @id)
);
RETURN @total;
END;

I get several types of errors when I try to create this function, like incorrect syntax near 'integer', Must declare the scalar variable "@total"., and Incorrect syntax near 'END'.

I've tried several variations and looked at several SO questions, but haven't found one that's fixed this issue for me yet. This is on SQL Server.

Upvotes: 1

Views: 3522

Answers (4)

Sean Lange
Sean Lange

Reputation: 33581

As previously mentioned the cause of your error is you did not define a datatype for your inbound parameter.

I would suggest a bit of a departure from the current structure. Instead of a scalar function I would use an inline table valued function. Something along these lines.

CREATE FUNCTION dbo.getSum(@id int)
RETURNS table
AS RETURN

SELECT SUM(MyAmount)
from
(
    select SUM(Amount) as MyAmount
    from table1
    where id = @id
    UNION ALL
    select SUM(amount)
    from table2
    where id = @id

) x;

Upvotes: 2

tastydew
tastydew

Reputation: 697

Your data types should be INT and there are general syntax errors... see below.

CREATE FUNCTION dbo.getSum(@id AS INT) -- Declare your paramater AS (datatype)
RETURNS INT  -- No need for AS here, just start your block
BEGIN
DECLARE @total AS INT; 
SELECT @total = 
(
    (select SUM(Amount)
    from TableOne
    where id = @id)
    +
    (select SUM(amount)
    from TableTwo
    where id = @id)
);
RETURN @total;
END;

Upvotes: 0

Daniel Stackenland
Daniel Stackenland

Reputation: 3239

I think you need to declare the type of the parameter

CREATE FUNCTION dbo.getSum(@id int)

Upvotes: 0

vamsi
vamsi

Reputation: 352

Looks like you are missing the parameter type in the function definiton

Try with the following

CREATE FUNCTION dbo.getSum(@id int)
RETURNS integer --I've also tried decimal(x,x)
AS
BEGIN
DECLARE @total as integer; -- same here with decimal
SELECT @total = 
(
    (select SUM(Amount)
    from table1
    where id = @id)
    +
    (select SUM(amount)
    from table2
    where id = @id)
);
RETURN @total;
END;

Upvotes: 0

Related Questions