SQL Adding Function

Question

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.

Have you tried declaring two different variables, storing the sum of each table into each variable and then performing an addition on them? — tastydew
– tastydew, Commented May 19, 2016 at 14:46
Yes, and that also didn't work, with similar errors to what I posted above. — pjlamb12
– pjlamb12, Commented May 19, 2016 at 15:15

Sean Lange · Accepted Answer · 2016-05-19 15:18:53Z

2

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;

answered May 19, 2016 at 15:18

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

3 Comments

pjlamb12 Over a year ago

There were multiple errors, even after declaring the parameter type, but your suggestion with the UNION ALL is perfect, and goes right along with what I needed. Thanks!

pjlamb12 Over a year ago

So, I did have to make a minor edit from what you returned. The UNION ALL part of the function works fine, but I just had to return the whole thing as a table, without the outer select SUM(MyAmount) from part. It returns a table with one column, MyAmount, and then I can add those together.

Sean Lange Over a year ago

Cool. Glad you were able to at least get a solid solution from it. :)

vamsi · Accepted Answer · 2016-05-19 15:16:06Z

0

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;

answered May 19, 2016 at 15:16

vamsi

3521 gold badge3 silver badges11 bronze badges

1 Comment

pjlamb12 Over a year ago

I've updated my original question. I did forget the parameter type originally, but have added it and still getting the same errors.

Daniel Stackenland · Accepted Answer · 2016-05-19 15:16:19Z

0

I think you need to declare the type of the parameter

CREATE FUNCTION dbo.getSum(@id int)

answered May 19, 2016 at 15:16

Daniel Stackenland

3,2591 gold badge21 silver badges23 bronze badges

4 Comments

pjlamb12 Over a year ago

I noticed I hadn't done that after, but it still is causing issues. I'm not sure why, I've done the same thing on other functions and not getting that error.

Daniel Stackenland Over a year ago

I can create the function in my db if I add the datatype

Daniel Stackenland Over a year ago

what SQL Server version are you using?

pjlamb12 Over a year ago

I think 2014. I'd have to check, but I'm pretty sure it's 2014.

tastydew · Accepted Answer · 2016-05-19 15:18:43Z

0

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;

answered May 19, 2016 at 15:18

tastydew

6979 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL Adding Function

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related