Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
store procedure code as follows;
 
 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
 
--exec [OH_BatchWise_Collection_Report] 'B8753'
--exec [OH_BatchWise_Collection_Report] 'B10720' //pending amt
    
--this store procedure is used to get the batchwise fee collection report

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))  
 
begin tran
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
    --Getting the receipt details
select @BillNo = bill_no,@pendamt = bill_pend_amt from bill_file2 where cr_bill_no = @CrBillNo and bill_active = 'A'
SET @SNo = @SNo + 1
 
    begin tran
	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
 
    commit tran
    close Batchwise_cur
    deallocate Batchwise_cur
 
    fetch next from Batchwise into @stud_id, @stud_name, @CrBillNo
    end 
 
    commit tran
	close Batchwise
	deallocate Batchwise
  
   --SNO,Student ID,Student Name grouped into row wise for each student.

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 execute the above store procedure 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
 

i want to write another store procedure,
from this store procedure three tables are different from the above store procedure code.
 
bill_file2
receipt_file2
cheque_file2
 
for the above three tables how to write the store procedure code using the above store procedure and get the output as follows;
 

1 53247 VAZHAKADAVIL THANKAPPAN PRASAD 51214 22-Apr-11 7900 DD 178846
2 30044 KANDASAMY ANANDAN 51540 5-May-11 7900 DD 748094
Posted 23-Apr-13 1:47am
Edited 23-Apr-13 1:53am
Costica U5.7K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is the third instance of same question in 2 days. Please avoid posting it multiple times.
 
Previous two:
using one store procedure how to use multiple tables[^]
using one store procedure how to use multiple tables[^]
 
If someone can help and want to, they will answer your previous question. Please remove the duplicates.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 23 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100