如何将函数值返回到存储过程 [英] How to return function value to a stored procedure
问题描述
请处理资产负债表报告。我使用函数进行计算，之后我希望它将每个Ledger Type的余额返回到存储过程并将其插入临时表但是没有获得所需的结果。任何帮助将不胜感激。
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.
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
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.
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.
推荐答案
我在这里看到了几个问题：
- 您的函数会返回特定时间点的余额，直到该时间点为止。它根本不会返回任何内容。
- 该函数返回所有分类帐类型的总和，而不是分组
- 函数，临时表和光标对于功能
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
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根据需要进行调整。
最终如Piebald所建议的那样使用Between之间
此外，为TransactionType添加一个额外的字段是不必要的，只需使交易表中的所有债务为负。
< Update>
根据您更新的问题，这里有一个更新的查询：
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:
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根据需要添加条件，并将其包装在存储过程中。
< / Update>
Add conditions as necessary, and just wrap it in a stored procedure.
</Update>
这篇关于如何将函数值返回到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
