My store procedue as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Presea_RefresherSMS]
as
begin
declare @Course varchar(20),
@NoofStudents varchar(20),
@Rowcount int,
@batchid varchar(20),
@CourseDate varchar(10)
select @CourseDate =CONVERT(VARCHAR(10), GETDATE(), 101)
create table #TempTable (course varchar(10), Noofstudents varchar(10))
begin tran
declare courses Cursor for
select cmn_minor_code as Course_Name,cbm_batch_id as Batch_ID from co_batch_master where cbm_active <> 'D' and cbm_batch_start_dt = @Coursedate and cmn_minor_code in('RFPFF','R-AFF','RFPFF_C')
open courses
fetch next from courses into @Course,@batchid
while @@Fetch_status = 0
begin
begin tran
declare studentcount cursor for
select count(*) from batch_course_registration a,course_registration b
where b.cr_bill_no = a.cr_bill_no and a.bcr_batch_id = @batchid and b.cr_active = 'A'
open studentcount
fetch next from studentcount into @Rowcount
while @@Fetch_status = 0
begin
insert into #TempTable values(@Course,@Rowcount)
fetch next from studentcount into @Rowcount
end
close studentcount
deallocate studentcount
commit tran
fetch next from courses into @Course,@batchid
end
close courses
Deallocate courses
commit tran
select * from #TempTable
When i run the store procedure shows output as follows
Course Noofstudents
RFPFF 16
R-AFF 16
RFPFF_C 6
But i want the output in singe line as follows
Dear Faculty RFPFF 16,R-AFF 6,RFPFF_C 6 By VIT
for that i tried as follows
select 'Dear Faculty ' + @Course + 'candidates is' , + convert(varchar,@Rowcount) + ' By VIT' from #TempTable
But when i run shows output as follows
Dear Faculty RFPFF_C candidates is 6 By HIMT
Dear Faculty RFPFF_C candidates is 6 By HIMT
Dear Faculty RFPFF_C candidates is 6 By HIMT
only last course is coming. the RFPFF and R-AFF course is not coming but also space is coming.
please help me.
for that how can i do
Regards,
Narasiman P.