OK, I admit it sometimes I’m a little late to the party. Even though they’ve been around since SQL Server 2000, I haven’t had the pleasure of getting acquainted with User Defined Functions (UDFs) inside SQL Server. But now that I know about them, I can see how useful they can be. There are several kinds of UDFs, today let’s chat about the scalar types.
A scalar UDF returns one, and only one value from the function. You can pass in parameters, have it do everything from simple to complex calculations, then return a result. Let’s take a look at how one is coded, using the AdventureWorks database.
create function dbo.f_ContactFullName(@id as int)
declare @FullName varchar(101);
select @FullName = FirstName + ‘ ‘ + LastName
where ContactID = @id;
Running the above code will create your UDF. To confirm it worked, in SQL Server Management Studios Object Explorer open the tree under AdentureWorks. Go down to Progammability, Functions, Scalar-valued Functions and you should see the new function.
The purpose of this function is to take the contact ID (the Primary Key) and look up the name in the Person.Contact table. It then concatenates the first and last names for us and returns the full name.
We begin with a create function command, followed by the name of the function. I would suggest you adopt some naming scheme to identify your functions, to separate them from stored procedures or other built in functions. Some folks use ufn, some fn, I chose f_ for my prefix. At the end of the function is the parameter list, enclosed in parenthesis. If there had been more than one parameter, I would have used commas to separate them.
Next comes the return type, in this case I’m returning a varchar. Your function will then be enclosed in a begin…end construct. I’ve declared a variable to hold the return value (@FullName), and then run a simple select statement to get the name, concatenate it, and store it in our variable. Finally I use the return command to return the value back to the caller. And how do we call it?
Well, one simple way is to simply say “select functionname()” as in
(1 row(s) affected)
I passed in a contact id from the table (1) and it returned the full name of that person. What might be more useful though is to include the UDF as part of a select statement.
order by Person.Contact.ContactID
On the second line of the select statement I call my small function, to comprise the full name. And under it you can see the results.
I should mention there are a few tradeoffs to using user defined functions. First, they are not portable to other databases. That means if you ever moved your data to another database type they would not transfer, and would have to be written as something else. But I mean, really now. How many times have you ever moved your data to another database. It happens so seldom that I would not worry about it.
What you should be concerned over though is the performance trade offs. For example, looking at my example above each row winds up making two calls to the same table, one to fetch the record and a second to fetch it again inside the function to get the name. Not very efficient. On the other hand, if I was reading another table, this might be just as efficient as joining the tables, and easier to use.
, dbo.f_ContactFullName(ContactID) as OurCustomer
order by SalesOrderID
In this example I’ve created a very simple User Defined Function. However, you can get as complex as you need and create long, intricate functions. Given the right circumstances UDFs can add a new dimension to your code reuse libraries.