User Defined Functions in SQL Server

User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role. For example, we can call user defined function in a where clause or use a user defined function in a JOIN [Where UDF returns a result set].

SQL Server supports two types of User Defined Functions as mentioned below –

- Scalar Functions – The function which returns a Scalar/Single value.
- Table Valued Functions – The function which returns a row set of SQL server Table datatype. Table Valued Functions can be written as –

• Inline Table
• Multi-statement Table

We will explore these functions today. I am using SQL Server 2012 for this demonstration, although you can use SQL Server 2005, 2008, 2008 R2 as well.

I have preconfigured Northwind database on my SQL Server instance. We will be using the following tables for creating different User Defined Functions –

• Customers
• Employees
• Orders
• Order Details
• Products

Let’s start querying the above table. Open a new Query window and write the following commands –

Scalar Function

We will now create a scalar function, which returns the number of orders placed by a given customer. Write the following code in your query pad –



The above function returns an integer value. To test this function, we will write some code as shown below –



Let us see another example which will fetch the number of orders processed by an employee for a given year. Write the following function in our query pad –



We will test this function with different years for an employee as shown below –

Table Valued Functions

Now let’s try an Inline Table valued function. Inline Table valued functions can return a row set using SQL Server Table datatype. You cannot perform addition logic in inline table valued functions. We will fetch the product details purchased by a customer as shown below –



To test this example we will use a select statement as shown below –



Another example of the Inline Table Valued Function is as shown below –



To test this function, we will use different years as shown below –




We will now see a Multi-Statement Table Valued Function. This function can be used to perform additional logic within the function. The code is as shown below –



To use the Multi-Statement Table Valued function, use this code –



There are couple of limitations you must consider before creating User Defined Functions. Some of them are as shown below –

• You cannot modify the state of the database using UDFs
• Unlike Stored Procedures, UDF can return only one single result set
• UDF does not support Try-Catch, @ERROR or RAISERROR function

Summary – User-defined functions are routines which perform calculations, receive one or more parameters and return either a scalar value or a result set. In this article, we saw how to create User Defined Functions. We also saw how to use Scalar functions and Table Valued Functions [Inline Table Valued Functions and Multi-Statement Table Valued Functions].

Download the source code of this article (Github)