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.