create procedure uspGetLoginDetails( @Username varchar(30),@Password varchar(30))
as
begin try
select  UserName,Password from Corporate_Booking.Employee where UserName=@Username and Password=@Password
end try
begin catch
select ERROR_MESSAGE()
end catch



--create proc uspGetNameByID @EmpID int,@FName varchar(30) out,@LName varchar(30) out
--as
--Begin try
--select @FName=c.FirstName,@LName=c.LastName from HumanResources.Employee e join Person.Contact c
--on e.ContactID=c.ContactID where e.EmployeeID=@EmpID
--end try
--begin catch
--select ERROR_MESSAGE();
--end catch

create proc uspGetRoomDetails @City varchar(30),@DateOfBooking varchar(30),@RoomType varchar(30),@Slot int,@RoomID int out,@RoomName varchar(30),
@RoomType varchar(30),@Capacity int,@NoOfMachines int out,@TableType varchar(30) out,@FacilityName varchar(30) out,

as
Begin try
select @FName=c.FirstName,@LName=c.LastName from Rooms r join VenueDetails v on r.LocationId=v.LocationId
join RoomFacility rf on r.RoomID=rf.RoomID
join Facility f on f.Facility_ID=rf.Facility_ID
join BookingDetails bon r.RoomID=b.RoomId
where rf.ActiveStatus=true and
(@DateOfBooking!=b.BookingDate and @Slot!= b.Slot)
end try
begin catch
select ERROR_MESSAGE();
end catch

-- delete room procedure


create proc uspDelete @City varchar(30),@RoomName(30) varchar(30)
as
begin try
delete from Rooms  where VenueDetails.City=@City and Rooms.RoomName=@RoomName
return @@rowcount
end try
begin catch
select ERROR_MESSAGE()
end catch

-- room name selection from city selection\

create proc uspSelectCity @City varchar(30),@RoomName varchar(30) out
as
Begin try
select @RoomName=r.RoomName from Rooms r join VenueDetails v
on r.LocationID=v.LocationID where v.City=@City
end try
begin catch
select ERROR_MESSAGE();
end catch


--add room insert sp

create proc uspInsertCustomer @CustID int,@FName varchar(20),@LName varchar(20),
@Email varchar(20),@Mobile varchar(20),@AL1 varchar(20),@AL2 varchar(20),@Type char(1),@Aadhar varchar(20)
as
begin try
insert into Customer values(@CustID,@FName,@LName,@Email,@Mobile,@AL1,@AL2,@Type,@Aadhar)
return @@rowcount
end try
begin catch
select ERROR_MESSAGE(),ERROR_LINE()
end catch