Solved

Not getting SUM value returned from stored procedure

Posted on 2007-11-25
21
519 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

740 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