0

i have the following sql query i seem to be having this error "Incorrect syntax near the keyword 'INSERT'"

2
Contributors
3
Replies
17
Views
3 Years
Discussion Span
Last Post by Sizwe_1
0
ALTER procedure [dbo].[AddBooking]
@CheckinDate datetime,
@CheckOutDate datetime,
@RoomName nvarchar(50)
as
 set nocount on;
 declare @ID int, @checkin date, @checkout date
 select @ID= BookingNo, @checkin=CheckinDate, @checkout=CheckOutDate
 from Booking b
 where b.RoomName=@RoomName and (@CheckinDate between b.CheckinDate and b.CheckOutDate
 or @CheckOutDate between b.CheckinDate and b.CheckOutDate)


 if @ID is not null
 begin
 declare @error varchar(200);
 set @error= 'the room' + @RoomName+'can not be booked because it conflicts with the existing booking ' + convert(varchar(20), @ID) + ' that starts at ' + convert(varchar(30), @checkin) + ' and  end at ' + convert(varchar(30), @checkout)    + CHAR(13) + 'Please select another room'
 raiserror(@error, 16,1)
 return-1
 end
 Merge INTO Booking as Target
  using (select @RoomNAme as RoomNAme, @CheckIn as CheckInDate, @CheckOut as CheckOutDate) as Source
  ON Target.RoomName = Source.RoomName and (Source.CheckInDate between Target.CheckInDate and Target.CheckOutDate or Source.CheckOutDate between Target.CheckInDate and Target.CheckOuDate)
  when NOT MATCHED
  INSERT into Booking (RoomName, CheckInDate, CheckOutDate)
  values (source.RoomNAme, source.CheckInDate, source.CheckOutDate)
0

thank you it worked, next problem will be actually executing it in visual studion

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.