An Overview Of User Defined Functions In SQL Server

Introduction

In this article, we will learn how to create and use user-defined functions in SQL server. We write the function for reusability purposes to avoid complexity and duplication of code. User-defined functions are precompiled and ready for later use. Using SQL server, you can easily create, view, and modify the user-defined functions. SQL Server user-defined functions are routines that accept the parameters and perform the action such as complex calculation and return the result of that action as value.

The function accepts only input parameters
The functions don’t support the exceptional handling

Type of function

SQL provides two types of functions.

System-defined function
User-defined function

Benefits of user-defined functions

It supports modular programming
Create once and reuse any number of times
Once compiled, same execution plan we can reuse, It reduces the compilation cost
It reduces the network traffic

Why we use the function

There are benefits of using the function. Some of them are the following.

We can use a user-defined function in select as well as where class.
We can use a user-defined function used as rawest.
We can use a user-defined function as parameterizing views.

System-defined function

System-defined functions provided by Microsoft SQL Server itself.

User Defined function

In SQL Server there are three types of user-defined functions

Scalar-Valued Function
The function which returns the single value, It may or may not have the parameters.

Inline table-valued function
An inline table-valued function returns a row set of SQL server data type and specifies only the table keyword in the return clause, without table definition information.

The code inside the functions returns a single return statement that invokes the select statement.

Multi-Statement table valued function
This function returns the table variable as a result of an action performed by function and table variable should be explicitly declared and defined by whose value can be derived from multiple SQL statements.

As you know the built-in functions, before going to look at user-defined function, we look at one of the existing system functions. Let’s take the date function.

declare @datetime datetime = getdate()
print @datetime

The function may or not may have a parameter but it always returns the value.

Output

Now, we are going to understand the user-defined function.

For example Weekdays – for all days of the week it returns a sequential day of the week.

CREATE FUNCTION Weekdays(@Day varchar)
RETURNS INT -- in which datatype you will accept/return output
AS BEGIN
IF(@Day IS NULL)
RETURN 0;
DECLARE @Output INT –-Declare the veriable
--Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
SELECT @Output =
CASE WHEN SUBSTRING(@Day, 1,1) = 'M' THEN 1
WHEN SUBSTRING(@Day, 1,1) = 'Tu' THEN 2
WHEN SUBSTRING(@Day, 1,1) = 'W' THEN 3
WHEN SUBSTRING(@Day, 1,1) = 'Th' THEN 4
WHEN SUBSTRING(@Day, 1,1) = 'F' THEN 5
WHEN SUBSTRING(@Day, 1,1) = 'Sa' THEN 6
WHEN SUBSTRING(@Day, 1,1) = 'Su' THEN 7
ELSE
99 END
RETURN @Output;
END

Execute the below code,

PRINT Weekdays('Monday')

Output

Now, we are going to check if the function is created or not,

Now execute the below code dbo. The prefix of function (Data Base Owner).

PRINT [dbo].[Weekdays]('Monday')
PRINT [dbo].[Weekdays]('Friday')
PRINT [dbo].[Weekdays]('Jitendra')
declare @datetime datetime = getdate()
print @datetime

Output

Now we are going to create the table, for example, we are converting existing employee to uppercase. We need to create the table like below.

Employee1

CREATE TABLE [dbo].[Employee1](
[eid] [int] NOT NULL,
[ename] [varchar](20) NULL,
[designation] [varchar](20) NULL,
[mgrid] [int] NULL,
PRIMARY KEY CLUSTERED
(
[eid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Execute the below insert script to insert the data in table.

USE [ExampleDB]
GO
INSERT [dbo].[Employee1] ([eid], [ename], [designation], [mgrid]) VALUES (1, N'Shrimant', N'.Net Developer', 2)
INSERT [dbo].[Employee1] ([eid], [ename], [designation], [mgrid]) VALUES (2, N'Arun', N'.PHP Developer', 1)
INSERT [dbo].[Employee1] ([eid], [ename], [designation], [mgrid]) VALUES (3, N'Kishore', N'Java Developer', 1)
INSERT [dbo].[Employee1] ([eid], [ename], [designation], [mgrid]) VALUES (4, N'Madhav', N'Azure Dev & DBA', 3)
INSERT [dbo].[Employee1] ([eid], [ename], [designation], [mgrid]) VALUES (5, N'Jitendra', N'.Net Developer', 4)

Scalar valued function

Syntax

Create function Function_Name(@Parameter1 Datatype, @Parameter2 Datatype, etc.)
returns Datatype)
As
Begin
--{Function body or statement}
End

Example

Ex. Now we are going to create the Scalar user defined function. Execute the below snippet code.

Create function ConvertEmployeeinUppercase(@EmployeeId int)
returns varchar(50)
As
Begin
Declare @UpperVar varchar(100) –-Declare the veriable
Select @UpperVar = Upper(ename) from [ExampleDB].[dbo].[Employee1] where eid = @EmployeeId
return isnull( @UpperVar , '-')
End

Now, we will check if the user defined function is created or not,

Call or execute the user defined function using the below snippet code,

select [dbo].[ConvertEmployeeinUppercase](1)
select [dbo].[ConvertEmployeeinUppercase](2)
select [dbo].[ConvertEmployeeinUppercase](3)
select [dbo].[ConvertEmployeeinUppercase](15) –passing the emp id which not available

Output

Execute the below snippet code,

SELECT [eid]
,[ename]
,[designation]
,[dbo].[ConvertEmployeeinUppercase]([eid]) As Upppercase_Emp
FROM [ExampleDB].[dbo].[Employee1]

Output

Inline table-valued function

An inline table-valued function specifies only the table keyword in the return clause, it returns table record as per requirement.

Execute the below snippet code to create the inline table-valued function.

CREATE FUNCTION GetEmployeeInfo_Table(@EmployeeId int)
returns table return
SELECT * FROM [ExampleDB].[dbo].[Employee1] where eid = @EmployeeId

Now we will check if the user defined function is created or not,

Call or execute the table valued user defined function using the below snippet code.

select * from GetEmployeeInfo_Table(4)
select * from [GetEmployeeInfo_Table](5)
select * from [GetEmployeeInfo_Table](15)

Output

We can use the scalar function in the where clause as well.

Multi-Statement table valued function

This function returns the table variable as a result of the action performed by a function.

Table variable should be explicitly declared and defined by whose value can be derived from a multiple SQL statement.

Ex. Now we are going to create the multi-statement table value user defined function -- execute the below snippet code.

CREATE FUNCTION GetEmployeeInfo_Multi_Table() returns @Result_Table table(eid int, ename varchar(20), designation varchar(20))
AS BEGIN
INSERT INTO @Result_Table
SELECT [eid] ,[ename],[designation] FROM [ExampleDB].[dbo].[Employee1]
INSERT INTO @Result_Table values (6,'Tuks','.Net Developer')
return;
END

In the above example @Result_Table variable is defined and that table variable can be treated like a regular table. We can perform any select, insert, update, delete operation on the table.

Execute the below snippet code.

select * from GetEmployeeInfo_Multi_Table()

Output

Execute the below snippet code.

select * from [Employee1]

Output

Conclusion

In this article, you learned how to write and use a user defined function. I hope, it's helpful.

4 Comments

Join the conversation! Your thoughts help the community grow.

Farhan Ahmed8y ago
Hi i know all these system functions. I wanted user define function like calculate days,months and years between to date start date and end date.
- Jitendra Waghale8y ago
  Why need to create UDF for date calculation, you can use system function as well. It's easy to use.
- Jitendra Waghale8y ago
  Https://www.c-sharpcorner.com/UploadFile/f0b2ed/date-and-time-functions-in-sql-server/
- Jitendra Waghale8y ago
  Https://www.c-sharpcorner.com/blogs/datetime-functions-in-sql-server2