Click here to Skip to main content
15,914,419 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My store procedure as follows

SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
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

select @final


When i execute the store procedure error as follows
Incorrect syntax near the keyword 'else'.



The above error shows in below line as follows
SQL
if @Accommodation= ''
		set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword +  

	 ELSE (This Line)
        Set @final = 'Dear Student, ' + @keyword +  + @Hotelname+   +@Phoneno+  +@Roomrate+  '- Visit www.himtmarine.com for Accommodation  By HIMT'



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

Regards,
Narasiman P.
Posted
Updated 26-Feb-14 20:40pm
v2

Try this:

C#
if @Accommodation= ''
set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword + 

Remove +
 
Share this answer
 
SQL
set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword + 

In the above line remove the last '+'
SQL
set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword
 
Share this answer
 
v2
SQL
if @Accommodation= ''
set @final = 'Dear Student, Thanks for contacting HIMT. Please Check HIMT for ' + @Keyword
 
ELSE (This Line)
Set @final = 'Dear Student, ' + @keyword + ' ' + @Hotelname+ ' ' +@Phoneno+ ' ' +@Roomrate+ '- Visit www.himtmarine.com for Accommodation By HIMT'

As mentioned in other solution you need to remove after @keyword & secondly if you need to append space in between value you using + ' ' +.

I have made the correction the above line of code
 
Share this answer
 
v2
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900