My store procedure code as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Short_Code](@Keyword varchar(10))
as
begin
declare @Batchdate varchar(20),
@Coursefees money,
@coursecode varchar(20),
@CHK int,
@MSG varchar(100)
SET @CHK=0
SET @MSG=''
set @Batchdate = ''
set @Coursefees = ''
select @CHK=COUNT(*),@coursecode = b.course_code from Tb_Course_Keyword as b where
b.Keyword = @Keyword and b.Active <> 'D' group by b.course_code
IF @CHK=0
begin
SET @MSG= 'Invalid keyword'
end
else
begin
declare coursedate cursor for
select TOP 3 Batch_Date = convert(char(12),a.cbm_batch_start_dt,113)
from CO_BATCH_MASTER a, co_batch_number b where (b.cbn_batch_nos-(SELECT
COUNT(*) FROM BATCH_COURSE_REGISTRATION BCR,COURSE_REGISTRATION CR WHERE BCR.cr_bill_no = CR.cr_bill_no
and BCR.bcr_batch_id = b.cbm_batch_id AND CR.cr_active <> 'D'))>1 and a.cbm_active<>'D' and
a.cmn_minor_code= @coursecode and a.cbm_batch_id=b.cbm_batch_id and datepart(year,a.cbm_batch_start_dt) <> '3000' and
datepart(year,a.cbm_batch_start_dt) <> '4000' and datepart(year,a.cbm_batch_start_dt) <> '5000' and
datepart(year,a.cbm_batch_start_dt) <> '6000' and a.cbm_batch_start_dt >= getdate() and b.cbn_batch_no=1 and
1<(b.cbn_batch_nos-((SELECT COUNT(*) FROM BATCH_COURSE_REGISTRATION BCR,COURSE_REGISTRATION CR WHERE BCR.cr_bill_no
= CR.cr_bill_no AND BCR.bcr_batch_id = b.cbm_batch_id AND CR.cr_active <> 'D') + (isnull((select top 1 noofsheets from bulkbooking where
batchid=a.cbm_batch_id and active<>'D'),0)))) order by a.cbm_batch_start_dt
open coursedate
fetch next from coursedate into @Batchdate
while @@Fetch_status = 0
begin
set @Batchdate = @Batchdate + ','
select top 1 @Coursefees = isnull(crm_course_rate,0) from co_rate_master where cmn_minor_code = @coursecode
and crm_active <>'D' and (datediff(d, crm_eff_start_dt, getdate()) >= 0) and
(datediff(d, getdate(), crm_eff_end_dt) >= 0)
fetch next from coursedate into @Batchdate
end
close coursedate
deallocate coursedate
end
select @Batchdate
end
when i execute the store procedure output as follows
exec[Short_Code] 'APS'
24 Feb 2014 ,
But i want the above output as follows
Dear Student, thanks for contacting HIMT. Batch dates for ' APS '. '24 Feb 2014 ,
for getting output what changes i have to made in my above store procedure.