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),
@Room varchar(25),
@final varchar(max)
create table #TempTable(res varchar(max))
begin
insert into #Temptable
select ('Dear Students, Hotel Accommodation is ' + Hotel_Name + ',' +
Phoneno + ',' + Room_Rate + ' By HIMT') as res from Tb_Hotel_Accommodation where Active <> 'D' and Description = @Keyword
select * from #Temptable
end
when i execute the store procedure output as follows
exec [Shortcode_Accommodation] 'ACCOMMODATION'
Dear Students, Hotel Accommodation is SATKAR LODGE,9380197278/26425881/33570477,Single Non-AC 425,Double Non-AC 525,Double AC 1145 By HIMT
Dear Students, Hotel Accommodation is HOTEL RAAJ BHAAVAN,26404621/22,Single Non-AC 900,Double Non-AC 1200,Single AC 1100,Double AC 1200 By HIMT
But i want when i execute the store procedure output i want as follows
Dear Students, Hotel Accommodation is 1.SATKAR LODGE,9380197278/26425881/33570477,Single Non-AC 425,Double Non-AC 525,Double AC 1145,2.RAAJ BHAAVAN,26404621/22,Single Non-AC 900,Double Non-AC 1200,Single AC 1100,Double AC 1200 By HIMT
from my above store procedure what mistake i made.
regards,
narasiman P.