Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

UDF as Computed Column Part 2

By Dinesh Priyankara,

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.

Total article views: 7043 | Views in the last 30 days: 6
 
Related Articles
FORUM

Customize SQL Query

Customize

FORUM

For development/Application DBA's- Pls suggest , I need to filter data according to customer group(CustomerID) for each login

making changes in joins (to include customerId,wherever misisng) in various SP's may not be feasible...

ARTICLE

Creating a SSIS Custom Source Component

In this article author Paul Mu shows us how you can create a custom component for the new SQL Server...

BLOG

Download free SSMS Add-In to create custom folders for database objects

I’ve just finished promised SSMS add-in which enables creating of custom folders directly in SSMS Ob...

FORUM

Retrieving Unique Customers in SQL

Retrieving Unique Customers in SQL

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones