How to return function value to a stored procedure

Question

1.00/5 (1 vote)

See more:

Please am working on a balance sheet report. Am using function to do the computation after which i want it to return a balance for each Ledger Type to the stored procedure and insert it into a temporary table but am not getting the desire result. Any help will be appreciated.

SQL

Create Function fnCalculateBalance
(
	@TransDate DateTime,
	@LedName Varchar(30)
)
	Returns Money
As
Begin
	Declare	@CreditBala Money,
			@DebitBala Money,
			@Balance Money
			
	Set @CreditBala = (Select Sum(Amount) From TRANSACTIONS 
			Where TransactionDate = @TransDate AND LedgerName = @LedName AND      TransactionType = 'Credit')

	Set @DebitBala = (Select Sum(Amount) From TRANSACTIONS 
			 Where TransactionDate = @TransDate AND LedgerName = @LedName AND TransactionType = 'Debit')

	Set @Balance = (@CreditBala - @DebitBala)
	
	Return @Balance
End

THE STORED PROCEDURE

SQL

Create Proc prcBalanceSheetReport 
(
	@TransDate DateTime
)
As
Begin
	Declare @LedName Varchar(25),
			@Balance Money

	Create Table #BalanceSheet
	(
		LedgerType Varchar(30),
		Balance Money,
		CompanyName Varchar(50),
		Branch Varchar(30),
		WebAddress Varchar(50),
		ReportTitle Varchar(50)
	)

	Declare Cur Cursor For
	Select LEDGER_NAME
	From GENERAL_LEDGER
	
	Open Cur 
	Fetch Next From Cur Into @LedName
	While @@Fetch_Status = 0
	Begin
		Select @Balance = dbo.fnCalculateBalance(@TransDate,@LedName)
	
		Insert Into #BalanceSheet
	
		Select LedgerType,
			@Balance,
			'',--CompanyName
			'',--Branch
			'',--WebAddress
			''--ReportTitle
			--''--GrandTotal		
		From TRANSACTIONS
		Where LedgerName = @LedName 
		Group By LedgerType
		Fetch Next From Cur Into @LedName
	End
	Close Cur
	Deallocate Cur

	Select * From #BalanceSheet
End

Sample records for better understanding.
This is the table am work with.

SQL

CREATE TABLE TRANSACTIONS
(
	TransactionDate DateTime,
	LedgerNo INT NOT NULL,
	LedgerName Varchar(30)NOT NULL,
	LedgerType Varchar(30)NOT NULL,
	Category Varchar(20)NULL, 
	Narration Nvarchar(100) NULL,
	TransactionType Varchar(12)NOT NULL,
	Amount Money NOT NULL,
	UserName Varchar(25)NOT NULL
)

Let say i have this records in my table

(05 Jul 2014,1,Bank JFK,Bank Account,Assets,Cash Deposit,Credit,10000,ASD)As row 1
(06 Jul 2014,1,Bank JFK,Bank Account,Assets,Cash Deposit,Credit,3000,ASD)As row 2
(07 Jul 2014,1,Bank JFK,Bank Account,Assets,Cash Withdrawal,Debit,2500,ASD)As row 3
(07 Jul 2014,2,AAA Ltd,Sundry Creditors,Liabilities,Goods supply,Credit,5000,ASD)As row 4
(07 Jul 2014,3,Sun Ltd,Sundry Creditors,Liabilities,Goods supply,Credit,35000,ASD)As row 5
(08 Jul 2014,3,Sun Ltd,Sundry Creditors,Liabilities,Payment for Goods,Debit,5000,ASD)As row 6
(08 Jul 2014,4,Sales,Sales Account,Assets,Goods sold,Credit,23000,ASD)As row 7
(09 Jul 2014,4,Sales,Sales Account,Assets,Cash to bank,Debit,15000,ASD)As row 8

So after haven this,the result that i want the stored procedure to produce is this.

LedgerType           Balance       Category
Bank Accounts        10500         Assets
Sundry Creditors     35000         Liabilities
Sales Account        8000          Assets

As you can see i have group them based on their ledger type. I hope this will help.

THANKS.

Posted 14-Jul-14 8:45am

NanaKwame

Updated 14-Jul-14 21:13pm

Jörgen Andersson

v5

Add a Solution

Comments

PIEBALDconsult 14-Jul-14 15:21pm

The problem is likely in Where TransactionDate = @TransDate
Check the values.
You may want to use a BETWEEN.

Maciej Los 14-Jul-14 17:49pm

Not much information. Please, be more specific and provide more details...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2014-07-14T12:07:00

I see a couple of problems here:

Your function returns the balance at a specific point in time, not until that point in time. It's not likely that it will return anything at all.
The function is returning a sum of ALL Ledger Types, not grouped
The function, temp table and the cursor are unnecessary for the functionality

Have a look at this query:

SQL

Select  LedgerType,
        sum(
            case TransactionType
                when 'Credit' then amount
                when 'Debit' then -amount
            end
        ) Balance,
	'',--CompanyName
	'',--Branch
	'',--WebAddress
	''--ReportTitle
	--''--GrandTotal		
From    TRANSACTIONS
Where   LedgerName = @LedName 
    And TransactionDate <= @TransDate 
Group By LedgerType

Adjust as necessary.
Eventually use a Between as suggested by Piebald
Also, having an extra field for TransactionType is unnecessary, just make all Debits in the Transaction Table negative.
<Update>

As per your updated question here's an updated query:

SQL

CREATE TABLE TRANSACTIONS
 (
 TransactionDate DateTime,
 LedgerNo INT NOT NULL,
 LedgerName Varchar(30) NOT NULL,
 LedgerType Varchar(30) NOT NULL,
 Category Varchar(20) NULL, 
 Narration Nvarchar(100) NULL,
 TransactionType Varchar(12) NOT NULL,
 Amount Money NOT NULL,
 UserName Varchar(25) NOT NULL
 )

;insert into transactions values ('05 Jul 2014',1,'Bank JFK','Bank Account','Assets','Cash Deposit','Credit',10000,'ASD')
;insert into transactions values ('06 Jul 2014',1,'Bank JFK','Bank Account','Assets','Cash Deposit','Credit',3000,'ASD')
;insert into transactions values ('07 Jul 2014',1,'Bank JFK','Bank Account','Assets','Cash Withdrawal','Debit',2500,'ASD')
;insert into transactions values ('07 Jul 2014',2,'AAA Ltd','Sundry Creditors','Liabilities','Goods supply','Credit',5000,'ASD')
;insert into transactions values ('07 Jul 2014',3,'Sun Ltd','Sundry Creditors','Liabilities','Goods supply','Credit',35000,'ASD')
;insert into transactions values ('08 Jul 2014',3,'Sun Ltd','Sundry Creditors','Liabilities','Payment for Goods','Debit',5000,'ASD')
;insert into transactions values ('08 Jul 2014',4,'Sales','Sales Account','Assets','Goods sold','Credit',23000,'ASD')
;insert into transactions values ('09 Jul 2014',4,'Sales','Sales Account','Assets','Cash to bank','Debit',15000,'ASD')
;
Select  LedgerType,
        sum(
            case TransactionType
                when 'Credit' then amount
                when 'Debit' then -amount
            end
        ) Balance,
        category
From    TRANSACTIONS
--Where   LedgerName = @LedName 
--    And TransactionDate <= @TransDate 
Group By LedgerType,category

Add conditions as necessary, and just wrap it in a stored procedure.
</Update>