Not getting SUM value returned from stored procedure

probably something to do with NULL treatments - are there any NULLS in the amount field?

I ran your code and got perfect results. Are you sure your data actually represents a difference between debits/credits?


hth

hang on ... you need to specify @balance as an output parameter.
@balance money OUTPUT

Try

  SELECT @Debits = SUM (IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Debit '
  SELECT @Credits = SUM(IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Credit'
  SET @Balance = @Credits - @Debits
  RETURN @Balance

According to MSSQL 2000 BOL, RETURN can only take an integer_expression.  So you shouldn't be using it to return a value from an sp - it is really there for error codes (succes/failure type of thing.).  Try modifying the paramter line to specify @balance as an OUTPUT parameter, as I suggest above then when you call the procedure

declare @myBalance money
exec SO_getBalance @myBalance = @Balance
print @myBalance

Does that help??

Also Depending on how you want to Return the value -- I would normally use "SELECT @BALANCE" to return the value to my application.   Return I normally reserve for Functions (Except in insert & delete procedures)

No Nulls in amount field
Definitely a difference between debits and credits
Tried @Balance money output - no go
Tried messen1975 code - no go

PaultheBroker and messen1975, I think you are on to it, but I'm using C# to talk to the database and I'm not sure how to modify that - you may not be able to help in this are if you're not familiar with C#, but I'll put what I have for the C# code and now have for the SQL below. I'm still not getting a SUM result.
C#:

            double Balance = 0.0;
            SqlCommand TransactionGetBalanceCommand = new SqlCommand();
            TransactionGetBalanceCommand.CommandType = CommandType.StoredProcedure;
            TransactionGetBalanceCommand.Connection = m_DBConnection;
            TransactionGetBalanceCommand.CommandText = "SP_GetBalance";

            TransactionGetBalanceCommand.Parameters.AddWithValue("@Balance", DbType.Currency);
            try
            {
                Balance = Convert.ToDouble(TransactionGetBalanceCommand.ExecuteScalar());
            }

SQL:
ALTER PROCEDURE [dbo].[SP_GetBalance]
  @Balance money OUTPUT
AS
BEGIN
  DECLARE @Debits money
  DECLARE @Credits money

  SET @Debits = 0.0
  SET @Credits = 0.0

  SET NOCOUNT ON;

SELECT @Debits = SUM (IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Debit '
  SELECT @Credits = SUM(IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Credit'
  SELECT @Balance = @Credits - @Debits
END

Hey - try this  more efficient way of doing what you are trying to do - I've no idea if it will solve the problem but its 'better' SQL.... :)

You can get rid of all those @Credits and @Debits temporary variables.....

select @balance = 
SUM(case Transaction_type 
        when 'Credit' then Transaction_Amount
        when 'Debit then -Transaction_Amount
   end)
FROM Transaction

Select allOpen in new window

I don't see:
connection.Open();

The "SELECT @Balance as Balance" was the final key to getting it work, and I didn't end up having to change the C#, though I don't understand HOW it works. 'Balance' isn't declared anywhere. I was surprised when it compiled without error - more surprised when it worked. Messen1975 can you give quick explanation?
And thanks for suggestions on improving the code, PaultheBroker - I appreciate.

Here is the code I'd use:

        double Balance = 0.0;
            SqlCommand TransactionGetBalanceCommand = new SqlCommand();
            TransactionGetBalanceCommand.CommandType = CommandType.StoredProcedure;
            TransactionGetBalanceCommand.Connection = m_DBConnection;
            TransactionGetBalanceCommand.CommandText = "SP_GetBalance";

            try
            {
                m_DBConnection.Open();
.              SqlDataReader m_reader = TransactionGetBalanceCommand.ExecuteReader();
               DataTable tblBalance = new DataTable();
               tblBalance.Load(m_reader);
               m_DBConnection.Close();
               Balance = double.Parse( tblBalance.Rows[0][0].ToString());
            }
------------------

SQL:
ALTER PROCEDURE [dbo].[SP_GetBalance]
AS
BEGIN
  DECLARE @Debits money
  DECLARE @Credits money

  SET @Debits = 0.0
  SET @Credits = 0.0

  SET NOCOUNT ON;

SELECT @Debits = SUM (IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Debit '
  SELECT @Credits = SUM(IsNull([Transaction].[Transaction_Amount],0))
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Credit'
  SELECT @Balance = @Credits - @Debits
END

yeah - out of my league I'm afraid - I've no idea what .executeScalar() is doing.  The stored proc will be returning several things - the return code (which as we've said is an integer, normally used for error codes), a dataset if you are issuing a select statement (as you are now), and an output variable @balance.  Hopefully one of the other wise men will be able to tell you how to pick those up in C#....

The ExecuteScaler()  looks in the First Row, First Column of the returned data.  The OUTPUT does not return in a table version.

Here is the MSDN link to the ExecuteScaler()

http://msdn2.microsoft.com/EN-US/library/system.data.sqlclient.sqlcommand.executescalar.aspx

And here is an example of using both a databind and the output parameter.

http://support.microsoft.com/kb/320916

Definetly use Paul's SQL Solution its much more efficient then the one you were using previously.

Overhead of 1 SELECT

vs.

3 Variable
& 
3 Selects

(And I really need to start using the code snippets)

good working with you messen...I've bookmarked that link you sent :)

l8r - Paul

Y'all are too fast for me - I'll still be absorbing all of this tomorrow.
Thanks tons for the help.

Both solutions marked work with no changes to the C# code. There are good links and suggestions in the other comments as well.