Post
Ask Question

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

  1. Scalar-Valued Function
    The function which returns the single value, It may or may not have the parameters.
  1. 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.
  1. 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.

  1. declare @datetime datetime = getdate()  
  2. print @datetime  

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

Output

User Defined Functions in SQL Server

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. 

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

Execute the below code,

PRINT Weekdays('Monday')

Output

User Defined Functions in SQL Server

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).
  1. PRINT [dbo].[Weekdays]('Monday')  
  2. PRINT [dbo].[Weekdays]('Friday')  
  3. PRINT [dbo].[Weekdays]('Jitendra')  
  4. declare @datetime datetime = getdate()  
  5. print @datetime 

Output

User Defined Functions in SQL Server

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 

  1. CREATE TABLE [dbo].[Employee1](  
  2.     [eid] [int] NOT NULL,  
  3.     [ename] [varchar](20) NULL,  
  4.     [designation] [varchar](20) NULL,  
  5.     [mgrid] [int] NULL,  
  6. PRIMARY KEY CLUSTERED   
  7. (  
  8.     [eid] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  10. ) ON [PRIMARY]  

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

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

Scalar valued function

Syntax

  1. Create function Function_Name(@Parameter1 Datatype, @Parameter2 Datatype, etc.)   
  2. returns Datatype)  
  3.  As   
  4.  Begin  
  5. --{Function body or statement}  
  6. End   

Example

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

  1.  Create function ConvertEmployeeinUppercase(@EmployeeId int)   
  2.  returns varchar(50)  
  3.   As   
  4.   Begin  
  5. Declare @UpperVar varchar(100)   –-Declare the veriable  
  6. Select  @UpperVar =   Upper(ename) from [ExampleDB].[dbo].[Employee1]  where eid = @EmployeeId   
  7. return isnull( @UpperVar , '-')  
  8.  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, 
  1. select [dbo].[ConvertEmployeeinUppercase](1)  
  2. select [dbo].[ConvertEmployeeinUppercase](2)  
  3. select [dbo].[ConvertEmployeeinUppercase](3)  
  4. select [dbo].[ConvertEmployeeinUppercase](15) –passing the emp id which not available   

Output 

User Defined Functions in SQL Server

Execute the below snippet code,

  1. SELECT [eid]  
  2.       ,[ename]  
  3.       ,[designation]  
  4.       ,[dbo].[ConvertEmployeeinUppercase]([eid]) As Upppercase_Emp  
  5.   FROM [ExampleDB].[dbo].[Employee1]  

Output 

User Defined Functions in SQL Server

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.

  1. CREATE FUNCTION GetEmployeeInfo_Table(@EmployeeId int)   
  2. returns table return   
  3. 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.
  1. select * from GetEmployeeInfo_Table(4)  
  2. select * from [GetEmployeeInfo_Table](5)  
  3. select * from [GetEmployeeInfo_Table](15)  

Output

User Defined Functions in SQL Server

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.

  1. CREATE FUNCTION GetEmployeeInfo_Multi_Table() returns @Result_Table table(eid int, ename varchar(20), designation varchar(20))   
  2.  AS BEGIN  
  3.    INSERT INTO @Result_Table   
  4.    SELECT [eid] ,[ename],[designation] FROM [ExampleDB].[dbo].[Employee1]   
  5.    INSERT INTO   @Result_Table values (6,'Tuks','.Net Developer')  
  6.    return;  
  7.  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

User Defined Functions in SQL Server

Execute the below snippet code.

 select * from [Employee1]

Output

User Defined Functions in SQL Server

Conclusion

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


Similar Articles