Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Not getting SUM value returned from stored procedure

Table Transaction has Transaction_Type declared as char(6) and it can have the value Debit or Credit.
It has Transaction_Amount declared as money.
Stored Procedure (below) is always returning a value of 0.0.
I've tried it both with 'Debit' and 'Debit '. Same result.
USE [FRST]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].SP_GetBalance
  @Balance money
AS
BEGIN
  DECLARE @Debits money
  DECLARE @Credits money

  SET @Debits = 0
  SET @Credits = 0

  SET NOCOUNT ON;

  SELECT @Debits = SUM ([Transaction].[Transaction_Amount])
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Debit '
  SELECT @Credits = SUM([Transaction].[Transaction_Amount])
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Credit'
  SET @Balance = @Credits - @Debits
  RETURN @Balance
END
GO
0
mrnbnf
Asked:
mrnbnf
  • 8
  • 7
  • 5
  • +1
2 Solutions
 
PaultheBrokerCommented:
probably something to do with NULL treatments - are there any NULLS in the amount field?
0
 
fanopoeCommented:
I ran your code and got perfect results. Are you sure your data actually represents a difference between debits/credits?


hth
0
 
PaultheBrokerCommented:
hang on ... you need to specify @balance as an output parameter.
@balance money OUTPUT
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
messen1975Commented:
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

0
 
PaultheBrokerCommented:
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??
0
 
messen1975Commented:
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)
0
 
mrnbnfAuthor Commented:
No Nulls in amount field
Definitely a difference between debits and credits
Tried @Balance money output - no go
Tried messen1975 code - no go


0
 
messen1975Commented:
Try

USE [FRST]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].SP_GetBalance
  @Balance money
AS
BEGIN
  DECLARE @Debits money
  DECLARE @Credits money

  SET @Debits = 0
  SET @Credits = 0

  SET NOCOUNT ON;

  SELECT @Debits = SUM ([Transaction].[Transaction_Amount])
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Debit '
  SELECT @Credits = SUM([Transaction].[Transaction_Amount])
  FROM [Transaction]
  WHERE [Transaction].[Transaction_Type] = 'Credit'
  SET @Balance = @Credits - @Debits
  SELECT @Balance as Balance
END
GO
0
 
mrnbnfAuthor Commented:
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
0
 
PaultheBrokerCommented:
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

Open in new window

0
 
messen1975Commented:
I don't see:
connection.Open();
0
 
mrnbnfAuthor Commented:
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.
0
 
messen1975Commented:
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
0
 
PaultheBrokerCommented:
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#....
0
 
messen1975Commented:
The ExecuteScaler()  looks in the First Row, First Column of the returned data.  The OUTPUT does not return in a table version.
0
 
messen1975Commented:
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
0
 
PaultheBrokerCommented:
thanks messen - I guess we're all the wiser !!! :)

So you can just forget the whole @balance thing and just issue a simple SELECT statement
create proc SP_Get Balance
AS
SELECT Balance = SUM(case ...blah)
FROM TRANSACTION

Open in new window

0
 
messen1975Commented:
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)
0
 
PaultheBrokerCommented:
good working with you messen...I've bookmarked that link you sent :)

l8r - Paul
0
 
mrnbnfAuthor Commented:
Y'all are too fast for me - I'll still be absorbing all of this tomorrow.
Thanks tons for the help.
0
 
mrnbnfAuthor Commented:
Both solutions marked work with no changes to the C# code. There are good links and suggestions in the other comments as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now