i have the following sql query i seem to be having this error "Incorrect syntax near the keyword 'INSERT'"
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)
I'm a bit rusty on WHEN NOT but I think it needs to be
WHEN NOT MATCHED THEN INSERT INTO
thank you it worked, next problem will be actually executing it in visual studion
We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.