11 – Functions

In this post I will be discussing Built-in functions in SQL Server and showing you how to create a User-Defined function.

Featured Videos

• Built-In Functions
• User-Defined Functions

MS Access and SQL Server both uses built-in functions that enhance the ability to generate queries and manipulate data. Certain functions exist in both applications (e.g. the DateDiff function) whilst others only exist in only Access (DLookup, DSum). In order to turn a query that contains a DLookup statement into a usable view in SQL Server, we will need to provide alternative code for the function.

CREATE VIEW vwBookingDetailsByBookings AS
SELECT 
tblBookingDetails.BookingDetailID, 
tblBookingDetails.BookingID_FK, 
tblBookingDetails.CheckInDate, 
tblBookingDetails.CheckOutDate, 
tblBookingDetails.RoomID_FK, 
tblBookingDetails.Notes, 
(SELECT r.CostPerNight FROM HotelBookingsDB.dbo.tblRooms r WHERE RoomID = RoomID_FK) AS CostPerNight,
DateDiff("d",CheckInDate,CheckOutDate) AS Duration,
(SELECT r.CostPerNight FROM HotelBookingsDB.dbo.tblRooms r WHERE RoomID = 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
FROM tblBookingDetails;

User-Defined Functions

Although we can use a SELECT statement in place of a function, we can also create our own function in SQL Server. This can help us to break the code up and make maintenance much easier.

--FN indicates a scalar function
--drop the function if it already exists
IF object_id(N'getRoomCost', N'FN') IS NOT NULL
    DROP FUNCTION getRoomCost
GO
--create the function @ID is a variable of data type int
CREATE FUNCTION getRoomCost(@ID int)

--The data type of the return value is money
RETURNS money
AS
BEGIN
	--declare a variable to return
	DECLARE @ret money;

	--This is how to generate an SQL statement in a function
    SELECT @ret = r.CostPerNight
    FROM HotelBookingsDB.dbo.tblRooms r
    WHERE RoomID = @ID	
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN (@ret);
END;

CREATE VIEW vwBookingDetailsByBookings AS
SELECT tblBookingDetails.BookingDetailID, 
tblBookingDetails.BookingID_FK, 
tblBookingDetails.CheckInDate, 
tblBookingDetails.CheckOutDate, 
tblBookingDetails.RoomID_FK, 
tblBookingDetails.Notes,
DateDiff("d",[CheckInDate],[CheckOutDate]) AS Duration,
dbo.getRoomCost(RoomID_FK) AS CostPerNight,
(SELECT r.CostPerNight FROM HotelBookingsDB.dbo.tblRooms r WHERE RoomID = 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 
FROM tblBookingDetails;

It can take some effort to learn functions in SQL Server but it is definitely worth persevering with.