SQL 201 — User Defined Functions

Back in SQL 101, I introduced you to some functions, GETDATE(), ISNULL(string1, string2), etc.  All of these functions come defined in Microsoft SQL before you even install it on your machine.  But that’s just the beginning.  You can create your own functions to do tasks too!  There are two types of user defined functions, table value functions and scalar value functions.

Table value functions return a table as a result, scalar values return only one result.  GETDATE() would be a scalar value function.  I’ll get into table value functions a bit later.

And if you see me use the abbreviation UDF, that’s a reference to user defined function.  I’ll start with a function that reminds me of my early days in programming.  The factorial function!

CREATE FUNCTION dbo.Factorial ( @inputNumberint )
DECLARE @i	int

	IF @inputNumber <= 1
		SET @i = 1
		SET @i = @inputNumber * dbo.Factorial( @inputNumber - 1 )

As you can see in my example, functions are very similar to stored procedures.  Both support input parameters, and are made of T-SQL statements, but there are some differences.  User defined functions must return one and only one result set (this result can be a scalar value or a table).  I chose factorial for a reason other than it reminds me of the early days.  I wanted to show you that just like all queries, UDFs can call other functions.  In this case it does so because this function is recursive!  If you’re not familiar with it, see this.

Now, for an example of a table value function, let’s say you had a table that had names and phone numbers in it.  Call that table personPhone.  If you wanted to make a function that did the reverse number lookup, you could create this function.

CREATE FUNCTION dbo.reverseNumberLookup ( @phoneNumber varchar(10))
      fname, mname, lname
   FROM personPhone
      phoneNumber = @phoneNumber

Again, this is like a stored procedure, it accepts parameters, and is made of multiple T-SQL statements.  The difference is one one record set can be returned.

Now you have the basics of user defined functions.  If you want to know more, please let me know! I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *