SQL - User Defined Functions

This article covers all the basics of User Defined Functions. It discusses how (and why) to create them and when to use them. It talks about scalar, inline table-valued and multi-statement table-valued functions. 

With SQL Server 2008, Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

What Kind of User-Defined Functions can I Create?

There are three types of UDF in Microsoft SQL Server 2008:

• Scalar functions.
• Inline table-valued functions.
• Multistatementtable-valued functions.

How do I create and use a Scalar User-Defined Function?

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp,image and user-defined data types.You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

CREATE FUNCTION whichContinent

(@Country nvarchar(15))

RETURNS varchar(30)

AS

BEGIN

declare @Return varchar(30)

select @return = case @Country

when 'Argentina' then 'South America'

when 'Belgium' then 'Europe'

else 'Unknown'

end

return @return

end

Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name. 

print dbo.WhichContinent('USA')

create table test (Country varchar(15), Continent as (dbo.WhichContinent(Country)))

insert into test (country) values ('USA')

select * from test

Country                Continent

USA                      Unknown

Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.

How do I create and use an Inline Table-Value User-Defined Function?

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables. The following code shows the fnGetEmployeesByCity UDF, which accepts a city and returns a table containing all employees' first name, last name, and address:

CREATE FUNCTION fnGetEmployeesByCity (@sCity VARCHAR(30))

    RETURNS TABLE

AS

RETURN

    (

        SELECT    FirstName, LastName, Address

        FROM    Employees

        WHERE    City = @sCity

    )

GO

This inline table value-returning UDF can be selected from or even joined to because it returns a rowset via the table datatype, as shown here:

SELECT * FROM dbo.fnGetEmployeesByCity('seattle')

A more complicated use of a UDF that returns multistatement tables could involve retrieving all employees by city, but if no customers match the given city then a dummy row is returned where the Address field is filled with "No matching employees found in the specified city," as shown below :

CREATE FUNCTION fnGetEmployeesByCity3 (@sCity VARCHAR(30))

    RETURNS @tblMyEmployees TABLE

    (

        FirstName VARCHAR(20),

        LastName VARCHAR(40),

        Address VARCHAR(120)

    )

AS

BEGIN

    INSERT    @tblMyEmployees

    SELECT   FirstName, LastName, Address

    FROM     Employees

    WHERE    City = @sCity

    ORDER BY LastName

    IF NOT EXISTS (SELECT * FROM @tblMyEmployees)

        INSERT @tblMyEmployees (Address)

            VALUES ('No matching employees found in the specified city')

       

    RETURN

END

GO