using one store procedure how to use multiple tables

See more:

SQL-Server

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_file2 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_file2 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_file2 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] 'B10720 ' output as follows;

1	58172     RALPH BENNET .R 1572	21 Apr 2012  100000.00	DD     264287              	
			          1573	21 Apr 2012  59000.00	DD     875452   0.00

           	
2	58167     ROBIN S.R	1564	21 Apr 2012  59000.00	DD     239301              	
			        1890	24 Jun 2012  100000.00	DD     373534    0.00



                     --Second Store Procedure 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;


1 53247 VAZHAKADAVIL THANKAPPAN PRASAD 51214 22 Apr 2011 7900.00 DD 178846

2 30044 KANDASAMY ANANDAN 51540 05 May 2011 7900.00 DD 748094




But i want to write the both the store procedure in to single store procedure and get the ouput.


how can i do using sql server 2000.


Regards,
Narasiman P.