使用一个存储过程如何使用多个表 [英] using one store procedure how to use multiple tables
问题描述
我的商店程序代码如下;
My Store Procedure code as follows;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[OH_BatchWise_Collection_Report](@BatchId varchar(10))as
begin
declare @SNo int,
@stud_name varchar(100),
@stud_id varchar(100),
@CrBillNo varchar(20),
@BillNo varchar(20),
@Rcptno varchar(20),
@Rcptdt varchar(20),
@RcptAmt varchar(20),
@Chqtype varchar(20),
@chqnum varchar(20),
@pendamt varchar(20)
create table #TempTable(SNo int, Stud_ID varchar(10), Stud_Name varchar(100),
Rcptno varchar(20),Rcptdt varchar(20), RcptAmt varchar(20),Chqtype varchar(20),chqnum varchar(20),pendamt varchar(20))
declare Batchwise cursor for
select s.stud_id, s.stud_name, cr.cr_bill_no from course_registration cr, batch_course_registration bcr, student s
where cr.stud_id = s.stud_id and bcr.cr_bill_no = cr.cr_bill_no and cr.cr_active = 'A'
and bcr.bcr_batch_id = @BatchId
SET @SNo = 0
open Batchwise
fetch next from Batchwise into @stud_id, @stud_name, @CrBillNo
While @@Fetch_status = 0
begin
select @BillNo = bill_no,@pendamt = bill_pend_amt from bill_file where cr_bill_no = @CrBillNo and bill_active = 'A'
SET @SNo = @SNo + 1
declare Batchwise_cur cursor for
select r.rcpt_no, convert(char(12),r.rcpt_dt,106) as Rcptdt, r.rcpt_amt from receipt_file r where r.bill_no = @BillNo
open Batchwise_cur
fetch next from Batchwise_cur into @Rcptno, @Rcptdt, @RcptAmt
while @@Fetch_status = 0
begin
set @Chqtype = ''
set @chqnum = ''
select @Chqtype = chq_type, @chqnum = chq_num from cheque_file where rcpt_no= @Rcptno
insert into #TempTable values(@SNo, @stud_id, @stud_name, @Rcptno,@Rcptdt,@RcptAmt,@Chqtype,@chqnum,@pendamt) --added
fetch next from Batchwise_cur into @Rcptno, @Rcptdt, @RcptAmt
end
close Batchwise_cur
deallocate Batchwise_cur
fetch next from Batchwise into @stud_id, @stud_name, @CrBillNo
end
close Batchwise
deallocate Batchwise
SELECT CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), sno) ELSE '' END AS sno, CASE WHEN RowNo =1 THEN CONVERT(VARCHAR(10), stud_id) ELSE '' END AS stu_id,
CASE WHEN RowNo =1 THEN [stud_name] ELSE '' END AS [stud_name],[Rcptno], [Rcptdt], [RcptAmt], [Chqtype], [chqnum],CASE WHEN RowNo =2 THEN CONVERT(VARCHAR(10), pendamt) ELSE '' END AS pendamt
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY sno ORDER BY sno) AS RowNo
FROM #TempTable
) AS T
end
我执行以上商店程序输出时如下;
exec [ OH_BatchWise_Collection_Report]''B8753''输出如下;
when i executing above store procedure output as follows;
exec [OH_BatchWise_Collection_Report] ''B8753'' output as follows;
Sno studentid stdname Rcptno Rcptdt RcptAmt Chqtype chqnum
1 53247 VAZHAKADAVIL 51214 22 Apr 2011 7900.00 DD 178846
2 30044 KANDASAMY 51540 05 May 2011 7900.00 DD 748094
使用上面的存储过程我使用如下表格
course_registration，batch_course_registration，student，bill_file，receipt_file，cheque_file并得到上面的输出。
以上商店程序我想使用如下表格
course_registration，batch_course_registration，学生，bill_file2，receipt_file2，cheque_file2并在上面的存储过程中获得如下输出。
在上面的存储过程中如何使用下表如下
bill_file2，receipt_file2，cheque_file2
当我执行时，exec [OH_BatchWise_Collection_Report] ''B8753''输出如下
using the above store procedure i used tables as follows
course_registration , batch_course_registration , student , bill_file, receipt_file, cheque_file and get the above output.
in the above store procedure i want to use table as follows
course_registration , batch_course_registration , student , bill_file2, receipt_file2, cheque_file2 and get the below output as follows in the above store procedure.
in the above store procedure how to use below tables as follows
bill_file2, receipt_file2, cheque_file2
when i execute, exec [OH_BatchWise_Collection_Report] ''B8753'' output as follows
Sno studentid stdname Rcptno Rcptdt RcptAmt Chqtype chqnum
1 53247 VAZHAKADAVIL 51214 22 Apr 2011 7900.00 DD 178846
2 30044 KANDASAMY 51540 05 May 2011 7900.00 DD 748094
当我执行时，exec [OH_BatchWise_Collection_Report]''B8753''输出如下
Sno studentid stdname Rcptno Rcptdt RcptAmt Chqtype chqnum待定amt
1 58172 RALPH 1572 2012年4月21日100000.00 DD 264287
1573 2012年4月21日59000.00 DD 875452 0.00
如何获得上述输出写入存储过程一次
如何使用sql server 2000.
请帮帮我
问候
narasiman P.
when i execute, exec [OH_BatchWise_Collection_Report] ''B8753'' output as follows
Sno studentid stdname Rcptno Rcptdt RcptAmt Chqtype chqnum Pending amt
1 58172 RALPH 1572 21 Apr 2012 100000.00 DD 264287
1573 21 Apr 2012 59000.00 DD 875452 0.00
how to get the above output writing store procedure in once
how can i do using sql server 2000.
Please help me
regards
narasiman P.
推荐答案
这是同一个问题的第二个实例（out 3）在2天内。请避免多次发布。
前两个：
使用一个商店程序如何使用多个表 [ ^ ]
如何使用内部联接将来自两个以上表的数据转换为单个结果集在商店程序 [ ^ ]
如果有人可以提供帮助，他们会回答您之前提出的问题。请删除重复项。
This is the second instance of same question(out of 3) in 2 days. Please avoid posting it multiple times.
Previous two:
using one store procedure how to use multiple tables[^]
how to use inner join to take the data from more than two tables into single result set in store procedure[^]
If someone can help and want to, they will answer your previous question. Please remove the duplicates.
这篇关于使用一个存储过程如何使用多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
