User Defined Functions in SQL Server

In this article, I am giving a quick overview about user defined functions in SQL Server. After completing this article you will understand:

What is User Defined Functions (UDF) in SQL Server?

What are the types of User Defined Function in SQL Server?

How to create User Defined Function in SQL Server?

How to ALTER and DROP user defined functions in SQL Server?

What are the advantages of User Defined Functions in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about TSQL programming basics, I will recommend you to visit the article if you are not aware of TSQL.

I hope you have gone through the basics of TSL Programming basics, now we can start.

As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.

Sub Program blocks are of two types in SQL Server.

1. Procedures

2. Functions.

In this article I will be focusing on Functions, I have already discussed about Stored Procedure in my previous article.

There are two types of Functions in SQL Server

1. Built In Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
2. User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions.

What is User Defined Functions (UDF) in SQL Server?

A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions. For more about stored procedure and function refer the article Difference between Stored Procedure and Function

What are the types of User Defined Function in SQL Server?

Functions are of 3 types in SQL Server.

1. Scalar Functions
2. Inline Table Valued Functions
3. Multi-Statement Table Valued Functions.

How to create User Defined Function in SQL Server?

Let’s understand each type of User Defined Functions with one simple example.

Scalar Functions

A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.

Below is the syntax to create Scalar Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS Data-Type

WITH Function-Options

AS

BEGIN

Function-Body

RETURN Scalar-Expression

END

Function-Name– This is the function name, you have to remember this name.

@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.

Function-Options – Functions options can be any of these two

1. Encryption – Indicates that the Database Engine encrypts the catalog view columns that contains the text of the create function statement.
2. Schemabinding – Indicates that Functions is bound to the database object that it references. Object can’t be dropped until you drop the function or alter the Function without Schemabinding option.

Function-Body – This is the place where we write our logic.

Example

I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.

You can use below query to populate dummy data.

USE TEACHMESQLSERVER

GO

CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)

INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)

INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)

INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)

INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)

GO

SELECT * FROM EMP

Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.

SQL Code –

CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)

RETURNS MONEY

AS

BEGIN

DECLARE @TOTALSALARY MONEY

SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID

RETURN @TOTALSALARY

END

Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path. 

Now our task is to call recently created a scalar function.

Syntax for calling a Scalar Function:

SELECT <owner>.<function-name> (Parameters values)

Calling the MYSCALARFUNCTION function:

SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY

 As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.

Inline Table Valued Functions

Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.

Syntax

CREATE FUNCTION Function-Name

(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)

RETURNS TABLE

WITH Function-Options

AS

RETURN (SELECT STATEMENT)

Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.

SQL Code –

CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)

RETURNS TABLE

AS

RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)

Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path. 

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYINLINETABLEVALUEDFUNCTION function:

SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)

As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.

Multi-Statement Table Valued Functions

Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.

Below is the syntax to create Multistatement Table Valued Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS @Return_Variable TABLE <Table-Type-Definition>

WITH Function-Options

AS

BEGIN

Function-Body

RETURN 

END

Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.

SQL Code –

CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )

RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)

AS

BEGIN

DECLARE @SALARY MONEY,@ANNUALSALARY MONEY

SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)

SET @ANNUALSALARY= @SALARY*12

INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)

RETURN

END

Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYMSTABLEVALUEDFUNCTION function:

SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)

As you can see from the above result set our recently created function is returning a table variable with two columns.

How to ALTER and DROP user defined functions in SQL Server?

ALTER Function

Once you have created your functions in SQL Server, you might want to make some changes into it.

You can ALTER User Defined Functions using ALTER Statement.  At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.

Drop Function

Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.

Syntax

DROP FUNCTION FUNCTION-NAME

Example

DROP FUNCTION MYSCALARFUNCTION

What are the benefits of User-Defined Functions?

The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.

What is difference between Function and Stored Procedure?

I have created a separate article for this topic.  Please click here to understand what are the differences between Stored Procedure and User Defined Functions in SQL Server?

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!