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