USE [WSS_Custom__08_29_2015]
GO
/****** Object:  StoredProcedure [dbo].[InsertUpdateDeleteHotels]    Script Date: 09/07/2015 13:04:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--InsertUpdateDeleteHotels '','',0,'','',1,0
-- =============================================
ALTER PROCEDURE [dbo].[InsertUpdateDeleteHotels]
-- Add the parameters for the stored procedure here
@hotelName varchar(50)='',
@hotelDesc varchar(MAX)='',
@cityId int=0,
@caretakerName varchar(50)='',
@caretakerContact varchar(50)='',
@Id int=0,
@update int=0,
@cnt int=0,
@hotelemail varchar(100) = ''


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
    set @cnt=(select COUNT(room_id) Present from room_master where hotel_id=@Id and room_deleted=0)
    if @cnt=0
    
 BEGIN
 
if @Id=0
 insert into hotels(hotel_name,hotel_desc,city_id,caretaker_name,caretaker_contact,hotel_email_id)
  values(@hotelName,@hotelDesc,@cityId,@caretakerName,@caretakerContact,@hotelemail)
  
else
begin
  If @update=0
  update hotels set hotel_deleted=1 where hotel_id=@Id
  else
update hotels set hotel_name=@hotelName,
  hotel_desc=@hotelDesc,
  city_id=@cityId,
  caretaker_name=@caretakerName,
  caretaker_contact=@caretakerContact,
  hotel_email_id = @hotelemail
 where hotel_id=@Id
  
end  

END
else
if @update=1
 update hotels set hotel_name=@hotelName,
  hotel_desc=@hotelDesc,
  city_id=@cityId,
  caretaker_name=@caretakerName,
  caretaker_contact=@caretakerContact,
  hotel_email_id = @hotelemail
 where hotel_id=@Id
select @cnt present

the SP looks good, what is the problem? any error message?