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 ENDAgain, this is like a stored procedure, it accepts parameters, and is made of multiple T-SQL statements. The difference is one

onerecord 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!

Permalink