How to Create Functions in SQL Server

Two days ago, while working on the SQL query, my manager asked me to get the output in table-valued format. I created a function for the table and showed the output.

A function in SQL Server is nothing but giving the required output based on the user’s input. Whatever the inputs, the function or operation will be the same.

If I use the concat function, the output will be like the image below.

Let’s get started to deep dive into this concept. Here, we will see the types of functions and the difference between stored procedure and function.

Types of Functions in SQL Server

There are two types of functions, namely,

• System Function
• User Defined Function

System Function

System Function is also called a built-in function that is defined by the system. This built-in function will save time while performing the specific task. This system function works with the SELECT statement and calculates the values.

Functions used in SQL Server

• String Functions (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)
• Date and Time Functions (Datetime, Datetime2, smalldatetime)
• Aggregate Functions (COUNT, MAX, MIN, SUM, AVG)
• Mathematical Functions (ABS, POWER, PI, EXP, LOG)
• Ranking Functions (RANK, DENSE_RANK, ROW_NUMBER)

Below is the picture showing all the built-in database functions used in SQL Server.

Commonly Used System Function

• CAST – Convert value into specified datatype.
• CONVERT – It also converts a value into a specified datatype.
• ISNULL – It will return the value if the expression is NULL.
• ISNUMERIC – It will check whether the expression is numeric.
• IIF – If the condition is True, it will return the value or if the condition is false, then it will return is another value.

User Defined Function

We can define our function from system function based on our needs. That is what a user-defined function is. This user-defined function has two types, namely,

• Scalar function
• Table-valued function

Scalar Function

Functions have input parameters; we can define functions without input parameters, but they should return a value. If that returned value is a single row value, that is a scalar function.

Let’s create one function. Here, I am adding two numbers to create a function. @a int and @b int are the two numbers that we will subtract, and they will return a value.

Create Function Subnumbers (@a int, @b int)
Returns int
Begin
Return @a - @b
End

Now, give the query for subtracting two numbers where dbo is the schema name. If you do not mention the schema name, it will throw an error.

Select dbo. Subnumbers(10,5)

Let’s check another example with the table. Here, I already have a table named EmpDetails.

• YesorNo is the Function Name
• @salary int – Here, I am defining the salary details, an integer data type of the table EmpDetails.
• Declare @VALUE char(1) – Local Variable where it has to return a value for the created function related to the input.
• If the salary value exceeds or equals 45000, it will return ‘Y’. Otherwise, it will return ‘N’.

Create Function YesorNo (@salary int)
Returns char(1)
As
Begin
Declare @VALUE char(1)
If (@VALUE >= 45000)
set @VALUE = 'Y'
Else
set @VALUE = 'N'
Return @VALUE
End

Now, use the query below to execute the output. See the output below. We have given 35000, less than 45000, so it returns the output as N.

Select dbo.YorN(35000)

This is what scalar functions in SQL Servers are. To see this function, visit your database- > Programmability- > Functions – > Your Function name.

Table Valued Function

The table-valued function returns the table itself. This function is helpful whenever you want the output in table format. There are two types of table values function.

• Inline table-valued function
• Multi-valued table-valued function.

Inline Table valued function

Here I want to see the salary details for those getting 45000 using functions.

Create Function EmpD(@salary int)
Returns Table
As
Return select * from EmpDetails where salary = @salary

select * from dbo.EmpD(45000)

Multi-valued Function

The inline function will know the table details to return the function of table details. Here, the multi-valued function doesn’t see the table structure details, so we must define the table variable. I want two table details. Below is the query to execute.

Create Function dbo.Persons()
Returns @persondata Table (Id int, PersonName varchar(100))
As
Begin
Insert into @persondata
select EmployeeId, EmpName from EmpDetails
Insert into @persondata
select Hospitalid, Hospitalname from HospitalInfo
Return
End

Select * from dbo.Persons()

By creating the table variable name person data, we have executed the two table value details using a function.

Drop Function in SQL Server

Similar to drop table syntax, we can delete the function in SQL Server. Below is the syntax.

Drop Function Function_Name;

Advantages of using Functions in SQL Server

Below are the main advantages of using Functions in SQL Server. Namely,

• Complexity will be reduced
• Faster Execution
• Reusability
• Code reduction

Difference between Function and Stored Procedure

Functions	Stored Procedure
It will return the values	It may or may not return the values
Cannot perform DML operations	Will perform DML operations
Only input parameters	Both input and output parameters
Returns only one value	Returns multiple value
Table variables can be used but not temporary tables	Both table variables and temporary tables will be used.
Table variables can be used, but not temporary tables	Can call both function and stored procedure inside the stored procedure.

Conclusion

These are the functions in SQL Server. I trust this tutorial helps you create scalar and table-valued functions. It also covered the advantages and differences between stored procedures and functions.

Try from your end by following the proper syntax and using the schema name while executing.

You may like the following sql server tutorials:

• SQL Server Substring Function
• SQL Server Convert Datetime to date + Examples
• How to execute function in SQL with parameters