Solved

Not getting SUM value returned from stored procedure

Posted on 2007-11-25
21
510 Views
Last Modified: 2013-11-27
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
Comment
Question by:mrnbnf
  • 8
  • 7
  • 5
  • +1
21 Comments
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20347988
probably something to do with NULL treatments - are there any NULLS in the amount field?
0
 
LVL 11

Expert Comment

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


hth
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20347995
hang on ... you need to specify @balance as an output parameter.
@balance money OUTPUT
0
 
LVL 6

Expert Comment

by:messen1975
ID: 20347996
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20348011
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
 
LVL 6

Expert Comment

by:messen1975
ID: 20348023
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
 

Author Comment

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


0
 
LVL 6

Accepted Solution

by:
messen1975 earned 250 total points
ID: 20348029
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
 

Author Comment

by:mrnbnf
ID: 20348053
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20348061
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 6

Expert Comment

by:messen1975
ID: 20348062
I don't see:
connection.Open();
0
 

Author Comment

by:mrnbnf
ID: 20348072
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
 
LVL 6

Expert Comment

by:messen1975
ID: 20348075
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20348079
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
 
LVL 6

Expert Comment

by:messen1975
ID: 20348080
The ExecuteScaler()  looks in the First Row, First Column of the returned data.  The OUTPUT does not return in a table version.
0
 
LVL 6

Expert Comment

by:messen1975
ID: 20348086
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
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 250 total points
ID: 20348087
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
 
LVL 6

Expert Comment

by:messen1975
ID: 20348091
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20348099
good working with you messen...I've bookmarked that link you sent :)

l8r - Paul
0
 

Author Comment

by:mrnbnf
ID: 20348103
Y'all are too fast for me - I'll still be absorbing all of this tomorrow.
Thanks tons for the help.
0
 

Author Closing Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now