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 )
RETURNS INT
AS
BEGIN
DECLARE @i	int

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

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))
RETURNS TABLE
AS
BEGIN
   SELECT 
      fname, mname, lname
   FROM personPhone
   WHERE
      phoneNumber = @phoneNumber
END

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!