If you have already read my previous article about UDF, hope now you know very well how to add UDF as computed column in table and why and when should use. In this article, I will show you more thing about UDF and let's see how useful it is.

All of you know that we use CHECK constraint to handle valid value in table column

or in other word maintain domain integrity. In example I, I will show you how

to use UDF for CHECK constraint.

Example I:

Again I will take a table that I have designed for one of our clients. This

Customer table holds all data about client but let's take only few columns that

is describe customer name and whether the customer is active or not. Plus, another

table that holds order information.

Here is the code for the tables.

CREATE SCHEMA AUTHORIZATION dbo

CREATE TABLE Orders
(OrderID int IDENTITY(1,1) PRIMARY KEY,
CustomerID int REFERENCES Customers(CustomerID) NOT NULL,
Date datetime NOT NULL)
CREATE TABLE Customers
(CustomerID int IDENTITY(1,1) PRIMARY KEY,
ContactName varchar(100) NOT NULL,
ActiveCustomer bit DEFAULT(0))

Before we continue on UDF, let's see why I have used CREATE SCHEMA AUTHORIZATION.

CREATE SCHEMA AUTHORIZATION

What is CREATE SCHEMA AUTHORIZATION? Actually this facilitate to create tables,

views and grant permission with a single statement. You can see in above code,

Orders table has referenced the Customers table before creating the Customers

table. Likewise, you can grant permission to an object before the object is

created. As per BOL, creates a schema that can be considered as a conceptual

object containing definition of tables, views and permission. for more info,

see BOL.

Ok. let's get back to UDF. Now what I want is, before creating a order, must

check whether the customer is active or not. So, let's write a small UDF to

check the active status. After that let's add the UDF to the Orders table as

CHECK constraint.

Here is the complete code.

CREATE FUNCTION isCustomerActive (@CustomerID int)

RETURNS bit
AS
BEGIN
RETURN (SELECT ActiveCustomer FROM Customers WHERE CustomerID = @CustomerID)
END
GO
ALTER TABLE Orders
ADD CONSTRAINT ch_CustomerActive CHECK (dbo.isCustomerActive(CustomerID)=1)

Now you can do some insertion like below.

INSERT INTO Customers (ContactName, ActiveCustomer) VALUES ('Customer1', 1) -- id is 1

INSERT INTO Customers (ContactName, ActiveCustomer) VALUES ('Customer2', 0) -- id is 2
INSERT INTO Orders (CustomerID, [Date]) VALUES (1, getdate()) -- successful
INSERT INTO Orders (CustomerID, [Date]) VALUES (2, getdate()) -- unsuccessful

Example II:fn_getDuration

In this example, I will use UDF in view. This is the UDF I have used to get

the duration in time format for given starting time and ending time when dateDiff

cannot be used. Hope this UDF will be very useful to you all. For this example,

I will take a table called ProcessesRun that holds process id, starting time

and ending time. Let's take these time columns as string.

Here is the complete code.

CREATE TABLE ProcessesRun

(ProcessID char(5) NOT NULL,
StartingTime varchar(15) NOT NULL,
EndingTime varchar(15) NOT NULL)
CREATE FUNCTION dbo.fn_getDuration (@STime datetime, @ETime datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @Minutes smallint
IF CONVERT(int, DATENAME(hour, @STime)) > CONVERT(int, DATENAME(hour, @ETime))
SET @Minutes = (((24 - CONVERT(int, DATENAME(hour, @STime)))*60) - CONVERT(int, DATENAME(mi, @STime)))
+ (CONVERT(int, DATENAME(hour, @ETime))*60) + CONVERT(int, DATENAME(mi, @ETime))
ELSE
SET @Minutes = (((CONVERT(int, DATENAME(hour, @ETime)) - CONVERT(int, DATENAME(hour, @STime)))*60)
- CONVERT(int, DATENAME(mi, @STime))) + CONVERT(int, DATENAME(mi, @ETime))
RETURN REPLICATE('0', 4-LEN(@Minutes/60)) + CONVERT(varchar(4), (@Minutes/60)) + ':' + REPLICATE('0', 2-LEN(@Minutes%60)) + CONVERT(varchar(2), (@Minutes%60))
END
CREATE VIEW vw_Processes
AS
SELECT ProcessID, dbo.fn_getDuration(StartingTime, EndingTime) AS Duration
FROM ProcessesRun
INSERT INTO ProcessesRun VALUES ('AA501', '17:00:00', '05:00:00')
INSERT INTO ProcessesRun VALUES ('AA502', '7:00:00 AM', '1:00:00 PM')
INSERT INTO ProcessesRun VALUES ('AA503', '10:00:00', '16:00:00')
INSERT INTO ProcessesRun VALUES ('AA504', '23:00:00', '00:45:00')
SELECT * FROM vw_Processes

Now you have seen many ways to use UDFs. Next time I will show you some useful

built-in undocumented UDFs. I highly appreciate all your comments about this article. you can reach me

through dinesh@dineshpriyankara.com.