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
Solved

Not getting SUM value returned from stored procedure

Posted on 2007-11-25
21
518 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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