UDF as Computed Column Part 2

By Dinesh Priyankara, 2003/07/14

Total article views: 6359 | Views in the last 30 days: 49

Rate this |

Join the discussion |

Briefcase

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.