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.
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,
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
Here is the complete code.
CREATE FUNCTION isCustomerActive (@CustomerID int)
RETURN (SELECT ActiveCustomer FROM Customers WHERE CustomerID = @CustomerID)
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
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)
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))
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))
CREATE VIEW vw_Processes
SELECT ProcessID, dbo.fn_getDuration(StartingTime, EndingTime) AS Duration
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