Use like below
ALTER proc [dbo].[Hotel_Accommodation] (@Keyword varchar(10))
as
declare @Hotelname varchar(25),
@Phoneno varchar(25),
@Roomrate varchar(25),
@CHK int,
@MSG varchar(max),
@final varchar(max),
@coursecode varchar(20),
@Accommodation varchar(20)
SET @Keyword = UPPER(@Keyword)
create table #TempTable(Hotelname varchar(25),Phoneno varchar(25),Roomrate varchar(25))
begin tran
select @CHK=COUNT(*),@coursecode = b.course_code from Tb_Course_Keyword as b where
b.Keyword = @Keyword and b.Active <> 'D' group by b.course_code
IF @CHK=0
begin
SET @MSG= 'Invalid keyword'
end
select Hotel_Name,Phoneno,Room_Rate from Tb_Accommodation where Active <> 'D'
if @MSG = ''
begin
if @Accommodation= ''
set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword
else
Set @final = 'Dear Student, ' + @keyword + + @Hotelname+ +@Phoneno+ +@Roomrate+ '- Visit www.himtmarine.com for Accommodation By HIMT'
end
else
begin
set @final = 'Invalid Keyword. Sorry try again with valid keyword or visit www.himtmarine.com.SMS HIMT xxx to 56677. Eg HIMT Accommodation to 56677'
end