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
- 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()
- 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.
- Scalar Functions
- Inline Table Valued Functions
- 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
- Encryption – Indicates that
the Database Engine encrypts the catalog view columns that contains the
text of the create function statement.
- 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!
