从单一存储过程返回多个查询结果 [英] Return Multiple queries result from Single Store Procedure

问题描述

我正在处理(.rldc)报告.我必须通过使用以下三个表来创建报告(在会计中称为方分类帐):

1). TblCompany
2).TblInvoice(将包含在借方发票中)
3). TblReceipt(其中包括信贷额)

现在，用户将选择时间段(从A到B).

此外，我必须计算出A期之前的费用(公司期初余额+(所有发票的金额-所有收据的金额))，并且我必须显示在A和B期之间发生的所有交易(发票和收据).用户可以选择所有公司以查看所有公司的交易.

我只想通过以下Qurires来做这些事情:

I am working on (.rldc) reports. I have to create a report (Which is called party ledger in accountancy) by using three following tables:

1). TblCompany
2).TblInvoice (Which will be included for debit invoices)
3). TblReceipt(Which will be included for credit amount)

Now User will select period (from A to B).

Further I have to compute (Company Opening Balance + (All Invoice’s amount -All Receipt’s amount against company)) before period A. and I have to show all transactions happened between Period A and B (Invoice and receipt). User can select for all companies to view transactions for all companies.

I just want to do these things with following qurires:

Select tblRecpt.* from tblRecpt
where RecDebttyp='A' and RecDebtid=4 And RecFlag<>'C'
And (DATEADD(dd, 0, DATEDIFF(dd, 0, RecDat))>= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-16 00:00:00.000')))
And (DATEADD(dd, 0, DATEDIFF(dd, 0, RecDat))<= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-20 00:00:00.000')))

GO

Select * from tbBilling1
where Dbttyp='A' and Dbtid=4
And (DATEADD(dd, 0, DATEDIFF(dd, 0, CONVERT(datetime,tbBilling1.Date,104)))>= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-16 00:00:00.000')))
And (DATEADD(dd, 0, DATEDIFF(dd, 0, CONVERT(datetime,tbBilling1.Date,104)))<= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-20 00:00:00.000')))

Go
Declare @TtlOBal float
Declare @TtlRec float

SELECT @TtlOBal= (tblAgent.AgentOpBal + (SELECT SUM(tbBilling1.Amount) FROM tbBilling1 LEFT OUTER JOIN
                                                    tblAgent ON tbBilling1.Dbtid = tblAgent.AgentID
                          WHERE        (tbBilling1.Dbttyp = 'A')))from TblAgent where tblAgent.AgentId=4
Select @TtlRec=Sum(RecAmt) from tblRecpt
where (DATEADD(dd, 0, DATEDIFF(dd, 0, RecDat))>= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-18 00:00:00.000')))
And (DATEADD(dd, 0, DATEDIFF(dd, 0, RecDat))<= DATEADD(dd, 0, DATEDIFF(dd, 0, '2011-02-18 00:00:00.000'))) and   RecFlag<>'C' and
RecDebttyp='A' And RecDebtid=4

if(@TtlRec<>Null)
begin
Select @TtlOBal= @TtlOBal - @TtlRec
end
Select @TtlOBal

现在，我想在单个存储过程中使用这些查询.

问候，
Aman

Now I want to use these queries in single Store Procedure.

Regards,
Aman

推荐答案

哪种方法可以最好地完成代码或Sql存储过程?
SQL存储过程

请提供示例或示例，以获得更好的解决方案或任何想法.
根据建议，使用存储过程.您需要根据数据库编写查询并获得结果.没什么了.万一在编写SP时遇到问题，请在查询和问题/错误时提出问题，一定可以帮助您.

试试吧！

Which will best way to do these things Code or Sql Store procs?
SQL Stored Procedures

Please provide sample or example for better solutions or any idea..
As suggested, use stored procedure. You need to write your queries based on your DB and get the result. Nothing more in it. In case, while writing SP, you face issue, do ask a question with your query and issue/error, surely one would help.

Try!

这篇关于从单一存储过程返回多个查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！