using one store procedure how to use multiple tables

Question

 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

 

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 7  900.00     DD         748094 

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 7  900.00     DD         748094 

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.