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 –

tablequeries

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 –

scalar1

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

scalartest1

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 –

scalar2

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

scalartest2

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 –

tvf1

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

tvftest1

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

tvf2

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

tvftest2
clip_image001

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 –

clip_image003

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

tvfmultistatementtest

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)


5 comments:

Unknown said...

Awesome article to understand UDF concept with example.

Anonymous said...

Excellent Sir good job. 1st I see and learn completely about all types of UDFs

Unknown said...

Can i get the result of Multi-Statement Table Valued function query result to my mail id , dasari.vinodh@gmail.com

Unknown said...

best article for understanding the UDF(User Defined Function)

Unknown said...

Very good explanation. Thank you sir.