In this post I will be discussing Stored Procedures, how to create them and the benefits of using them.
Stored Procedures in SQL Server are similar to sub procedures in MS Access. We often have code that needs to be called in more than one location and rather than duplicate the code, it is preferable to keep it in a stored procedure and be able to call it from multiple locations. We are going to use the HotelBookingsDB to provide a solid working example of this.
Objective
We are writing a database for a hotel and the owners of the hotel would like to be able to generate a report that tells us which guests are currently staying in the hotel. Guests come and go on an hour-to-hour basis and so the report will need to be accurate at any point in time it is generated! This leaves us with two options:
1) Create a query that runs a function that returns false if guest is not staying and true if they are.
2) Add a static field to the guests table that will hold the above information and that gets updated every time a booking is made/cancelled.
Which one would you choose?
Both ideas have their merits but we have opted for the second option and here’s why:
If we utilise a query that contains a function, we will need to call this function every time we need to view the report. This is not always an problem, but what if we have one thousand guests (and counting) registered on the system? The query will get slower and slower as time progresses. If we have a static field in the guests table that indicates at any given moment if a guest is current, the report will take no time at all to generate. The only issue is ensuring that the data is kept up-to-date at all times. For this we need to use stored procedures and triggers (discussed in a coming post).
Preparation
The first thing we will need to do is to add a column to the guests table. We need to execute an SQL command to do that.
Open up your SQL Management Studio, right-click on HotelBookingsDB and select New Query.
Add in this code and click on the execute button.
ALTER TABLE tblGuests ADD IsCurrent bit DEFAULT 0 NOT NULL
This code will add a field called IsCurrent to tblGuests. The data type is bit which is the equivalent of the Yes/No data type in Access and will store 1 (True) and 0 (False). We have also given the field a default value of 0 (False).
Now we need to create a function that returns 1 if a guest is currently staying at the hotel or 0 if not. In order to ascertain this, we need to do evaluate whether the current date falls within the parameters of a booking check-in and check-out date. What?
If today’s date is 14 July 2013 and Max Beasley has a booking from the 1st July 2013 to the 15 July 2013, then Max is currently staying at the hotel. Had he left on the 13th July 2013, he would not be currently staying at the hotel. Simple, right? Well not exactly.
You see, the check-in and check-out details of a booking are stored in the BookingDetails table whilst the GuestID for that booking is stored in the Bookings table. Whatever will we do? Create a view of course.
If you are faced with a similar situation, create a view that consists of the two tables in question and you will be able to read them as a single data set. Here is the code for that:
CREATE VIEW vwBookingDetailsExtended AS
SELECT tblBookingDetails.BookingDetailID,
tblBookingDetails.BookingID_FK,
tblBookingDetails.CheckInDate,
tblBookingDetails.CheckOutDate,
tblBookingDetails.Notes,
dbo.getRoomCost(RoomID_FK) AS CostPerNight,
DateDiff("d",CheckInDate,CheckOutDate) AS Duration,
(dbo.getRoomCost(RoomID_FK))*(DateDiff("d",CheckInDate,CheckOutDate)) AS Total,
CONCAT(DateDiff("d",CheckInDate,CheckOutDate), ' nights @ £', dbo.getRoomCost(RoomID_FK),
' per night = £', (dbo.getRoomCost(RoomID_FK))*(DateDiff("d",CheckInDate,CheckOutDate))) as Cost,
tblBookings.GuestID_FK,
tblBookingDetails.RoomID_FK
FROM tblBookings INNER JOIN tblBookingDetails ON tblBookings.BookingID = tblBookingDetails.BookingID_FK;
The name of this view is vwBookingDetailsExtended and is similar to the vwBookingDetailsByBookings (vwBookingDetailsByBookings didn’t have all the information we required so we didn’t use it).
This is the data set that vwBookingDetailsExtended returns:
As can be seen, the CheckInDate, CheckOutDate and GuestID_Fk fields are all displayed in a single record.
So, now we need a function that will let us know if a guest is currently staying in the hotel, or not.
--FN indicates a scalar function
--drop the function if it already exists
IF object_id(N'getCurrent', N'FN') IS NOT NULL
DROP FUNCTION getCurrent
GO
--create the function @ID is a variable of data type int
CREATE FUNCTION getCurrent(@ID int)
--The data type of the return value is bit
RETURNS bit
AS
BEGIN
--declare a variable to return
DECLARE @ret bit;
--This is how to generate an SQL statement in a function
SELECT @ret = COUNT(BookingDetailID) FROM HotelBookingsDB.dbo.vwBookingDetailsExtended WHERE GuestID_FK=@ID AND
CheckInDate <= GETDATE() AND CheckOutDate >=GETDATE()
IF (@ret > 0)
SET @ret = 1;
else
set @ret = 0;
RETURN (@ret);
END;
We used the function we made in 11 – Functions as a template. In the above function we are counting the number of records that appear in vwBookingDetailsExtended where ID = @ID (parameter) and today’s date falls between the CheckInDate and CheckOutDate (inclusive). If there are 0 records we return 0. If there are more than 0 records we return 1.
Creating And Executing A Stored Procedure
Now we have added the necessary field to the guests table, created the view and created the function, we can create the stored procedure.
CREATE PROCEDURE dbo.UpdateCurrent(@GuestID integer) AS BEGIN IF @GuestID=-1 UPDATE HotelBookingsDB.dbo.tblGuests SET IsCurrent = dbo.getCurrent(@GuestID) ELSE IF @GuestID>0 UPDATE HotelBookingsDB.dbo.tblGuests SET IsCurrent = dbo.getCurrent(@GuestID) WHERE GuestID = @GuestID END
EXEC dbo.UpdateCurrent -1
Testing
Having created the stored procedure, it would be nice if we could test it thoroughly. The easiest way to do this (in this case), is to call the stored procedure from MS Access.
In the next post we will be showing you how.
