my store procedure is not working correctly

Question

i tried lot of times in my store procedure my required output is not coming correctly.

My store procedure as follows;

Field Datatype

Sno int

Desc varchar(50)

Hotelname varchar(MAX)

Phoneno varchar(50)

Roomrate varchar(50)

Active char(1)

In table record as follows

Sno Desc Hotelname Phoneno Roomrate Active

1 Accom1 Satarlodge 24745734/9840175805 SingleNonAC 500,Double AC 1000 A

2 Accom1 Sarvanalodge 24151212/9790578502 SingleNonAC 600 Double AC 1200 A

3 Accom2 Suryalodge 24851524/9852012312 SingleNonAC 1000 DoubleAC 1600 A

4 Accom2 kalpanlodge 24221222/9844121252 SingleNonAC 1000 DoubleAC 1600 A

My store procedure code as follows

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER proc [dbo].[Shortcode_Accom] (@Keyword varchar(10))

as

declare @Hotel varchar(25),

@Phone varchar(25),

@Room varchar(25),

@final varchar(max)

select @Hotel=Hotelname,@Phone=Phoneno,@Room =Roomrate from Tb_Accommodation where Active <> 'D' and Serialno =@keyword

if(@Hotel!='')

begin

Set @final = 'Dear Students HIMT Hotel Accommodation is ' +@Hotel+',' +@Phone+ ','+@Room+ ' by marine'

select @final

end

When i execute the above store procedure output as follows

exec [Shortcode_Accom] 'Accom2'

output as follows

Dear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200 by marine

But i want the output as follows

Dear Students HIMT Hotel Accommodation is kalpanlodge,24221222/9844121252,AC 1200,Suryalodge 24851524/9852012312,SingleNonAC 1000 DoubleAC 1600 by marine

because in the table For Accom2 two records are there. but when i execute in the store procedure only one record is showing.

please help me what is the problem in my above store procedure.

i tried lot of times please help me.