Store procedure code as follows;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--exec [OH_BatchWise_Collection_Report_Presea] 'B10720'
---- B9003
-- B8753
--this store procedure is used to get the batchwise fee collection report Psea
ALTER procedure [dbo].[OH_BatchWise_Collection_Report_Presea](@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
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
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;
sno stu_id name Rcptno rcptdt Rcptamt Chqtype Chqnum
1 58172 RALPH 1572 21 Apr 2012 100000.00 DD 264287
1573 21 Apr 2012 59000.00 DD 875452
From the above output i want to change the heading as follows;
sno stu_id name Rcptno rcptdt Rcptamt Ptype Number
1 58172 RALPH 1572 21 Apr 2012 100000.00 DD 264287
1573 21 Apr 2012 59000.00 DD 875452
how can i do please help me.
in the above store procedure code where i change the Ptype and Number instead of Chqtype Chqnum
Please help me.
Regards,
Narasiman P.