我想以单行显示我的商店程序输出 [英] I want to display my store procedure output in single line

问题描述

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[ProcedureName] 
as
begin

declare @Course varchar(20),
        @NoofStudents varchar(20),
        @Rowcount int,
        @batchid varchar(20),
        @CourseDate datetime

       set @CourseDate = getdate();
   
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
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,@batchid)
		     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
end

当我运行商店程序时显示输出如下

When i run the store procedure shows output as follows

exec [ProcedureName]

输出如下

Output as follows

course   Noofstudents
 AFF      10

从我的上面的输出我希望以单行显示如下

From my above output i want to display in single line as follows

Dear Faculty, AFF candidates is 10.

i希望以单行显示为安全。



我该怎么办sql。



问候，

Narasiman P.

i want to show in single line as senetence.

for that how can i do in sql.

Regards,
Narasiman P.

推荐答案

小建议：使用UI代替SQL



但是......

Little suggestion: use UI instead of SQL

But...

CREATE PROCEDURE GetInfo(@CourseName VARCHAR(30))
AS
BEGIN

DECLARE @tmp TABLE (CourseName VARCHAR(30), CountOfStudents INT)

INSERT INTO @tmp (CourseName, CountOfStudents)
SELECT C.CourseName, COUNT(S.StudentId) AS CountOfStudents
FROM CourseTable AS C INNER JOIN StudentTable AS S ON C.StuID = S.StuID
WHERE C.CourseName = @CourseName

DECLARE @stu INT
SELECT @stu = CountOfStudents
FROM @tmp

SELECT 'Dear Faculty, ' + @CourseName + ' candidates is: ' + CONVERT(VARCHAR(10), @stu) + '.' AS Info

END

这篇关于我想以单行显示我的商店程序输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！