Solved

Not getting SUM value returned from stored procedure

Posted on 2007-11-25
21
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

617 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