Need help with Stored Procedure

[acmeyer]

I have a stored procedure that I need to write where I need to join two tables, sum a field in each of the tables. I then need to add those two sum fields together to get one total to return to the program that called the stored procedure. I know how to work the Sum function but I don't know how to access the results of the Sum to add it together for the total.

any ideas?

 

[KrisSimonis]

Use this select statement:

SELECT
SUM(a.numField) + SUM(b.numField) AS totalSum
FROM tableA AS a
INNER JOIN tableB ON b WHERE a.id = b.id

this will return 1 row with 1 column that contains the grand total.

 

[acmeyer]

Thank you so much that solves the first part of my problem exactly. However I have one final twist that I just realized since posting. I will have 2 of these select statements like you have above and I will have to add the totalSum from the first to the second and use the RETURN function to return that amount. How do I get at the "AS" field. That is what has been bugging me from the beginning...i can't figure it out!


THANKS SO MUCH !!!!!

 

[KrisSimonis]

Do something like this:

CREATE PROCEDURE totalSum @sum BIGINT OUTPUT
AS
SELECT @sum = SUM(a.numField) + SUM(b.numField)
FROM tableA AS a
INNER JOIN tableB ON b WHERE a.id = b.id
GO

An adaption of a stored procedure in the manual btw.

rtm,
Kris Simonis