如何更改商店过程中的标题名称 [英] how to change the heading name in the store procedure
问题描述
存储程序代码如下;
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
GO
--exec [OH_BatchWise_Collection_Report_Presea]''B10720''
---- B9003
- B8753
- 这个商店程序用于获取批量收费报告Psea
ALTER程序[dbo]。[OH_BatchWise_Collection_Report_Presea](@ BatchId varchar(10))as
开始
声明@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)
创建表#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
声明批处理游标
从course_registration cr,batch_course_registration bcr,学生中选择s.stud_id,s.stud_name,cr.cr_bill_no s
其中cr.stud_id = s.stud_id和bcr.cr_bill_no = cr.cr_bill_no和cr.cr_active =''A''
和bcr.bcr_batch_id = @BatchId
SET @SNo = 0
打开Batchwise
从Batchwise下一步获取到@stud_id,@ stud_name, @CrBillNo
而@@ Fetch_status = 0
开始
select @BillNo = bill_no,来自bill_file2的@ pendamt = bill_pend_amt,其中cr_bill_no = @CrBillNo和bill_active =''A''
SET @SNo = @SNo + 1
begin tran
声明Batchwise_cur游标为
select rr cpt_no,将(char(12),r.rcpt_dt,106)转换为Rcptdt,来自receipt_file2的r.rcpt_amt r
其中r.bill_no = @BillNo
打开Batchwise_cur
从Batchwise_cur接下来获取@Rcptno,@ Rcptdt,@ RMptAmt
而@@ Fetch_status = 0
开始
set @Chqtype =''''
set @ chqnum =''''
从cheque_file2中选择@Chqtype = chq_type,@ chqnum = chq_num,其中rcpt_no = @Rcptno
插入#TempTable值(@SNo,@ stud_id,@ stud_name,@ Rcptno,@ Rcptdt,@ RcptAmt,@ Chqtype,@ chqnum,@ pendamt) - 添加
从Batchwise_cur获取下一个到@Rcptno, @Rcptdt,@ RMptAmt
结束
commit tran
关闭Batchwise_cur
deallocate Batchwise_cur
从Batchwise获取下一个@stud_id,@ stud_name,@ CrillNo
end
commit tran
关闭批处理
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 as stu_id,
CASE当RowNo = 1那么[stud_name] ELSE''''结束为[stud_name],[Rcptno],[Rcptdt],[RcptAmt],[Chqtype],[chqnum],CASE WHEN RowNo = 2那么CONVERT(VARCHAR(10),pendamt)ELSE''''END as pendamt
FROM(
SELECT *,ROW_NUMBER()OVER(由sno ORDER BY sno分割) AS RowNo
FROM #TempTable
)AS T
end
when我执行上面的存储过程输出如下;
sno stu_id名称Rcptno rcptdt Rcptamt Chqtype Chqnum
1 58172 RALPH 1572 2012年4月21日100000.00 DD 264287
1573 21 Apr 2012 59000.00 DD 875452
从上面的输出我想改变标题为跟随;
sno stu_id名称Rcptno rcptdt Rcptamt Ptype Number
1 58172 RALPH 1572 2012年4月21日100000.00 DD 264287
1573 2012年4月21日59000.00 DD 875452
怎么样我可以帮助我。
在上面的商店程序代码中,我改变了Ptype和Number而不是Chqtype Chqnum
请帮帮我。
问候,
Narasiman P.
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.
推荐答案
您可以使用AS
指定其名称来重命名最终结果中的列。类似于:
You can rename the columns in the end result by specifying their name usingAS
. Something like:
SELECT Chqtype AS PType
FROM MyTable
...
< br $> b $ b
祝你好运!
Good luck!
这篇关于如何更改商店过程中的标题名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!