存储过程输出为行 [英] Store procedure output to be in row wise

问题描述

我的商店程序如下

My store procedure as follows

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[Shortcode_Accommodation] (@Keyword varchar(50))
as

declare @Hotel varchar(25),
@Phone varchar(25),
@final varchar(max)

create table #TempTable(res varchar(max)) 
begin
insert into #Temptable(res) values('Dear Students, Hotel Accommodation is');
insert into #Temptable(res)
select CONVERT(varchar, ROW_NUMBER() over(order by Hotel_Name))+'. '+Hotel_Name+','+Phoneno+''
from Tb_Accommodation where Active <> 'D' and Description = @Keyword
insert into #Temptable(res) values('By marine');

select * from #Temptable 
end

执行上述存储过程输出时如下

when I execute the above store procedure output as follows

exec [Shortcode_Accommodation] 'Accommodation'

Dear Students, Hotel Accommodation is<br />
1. HOTEL RAAJ BHAAVAN,26404621/22<br />
2. HOTEL SHRI VALLI RESIDENCyY,26422297/42668009<br />
By marine

但我想要输出单线需要如下



亲爱的学生，酒店住宿是1.HOTEL RAAJ BHAAVAN，26404621/22 2.HOTEL SHRI VALLI RESIDENCY ，26422297/42668009由marine

推荐答案

alter proc [dbo].[Shortcode_Accommodation] (@Keyword varchar(50))
as
 
declare @Hotel varchar(25),
@Phone varchar(25),
@final varchar(max)
 
create table #TempTable(res varchar(max)) 
begin
Declare @AddHotelNames nvarchar(max)



select @AddHotelNames=COALESCE(@AddHotelNames+',','')+ ' '+CONVERT(varchar, ROW_NUMBER() over(order by Hotel_Name))+'. '+Hotel_Name+','+Phoneno+''
from Tb_Accommodation  where  Active <> 'D' Description = @Keyword

print  @AddHotelNames
set @AddHotelNames='Dear Students, Hotel Accommodation is'+@AddHotelNames+'By marine'

insert into #Temptable(res) values(@AddHotelNames);
 
select * from #Temptable 
end

这篇关于存储过程输出为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！