How to create functions in SQL Server Management Studio

In this sql server tutorial, we will learn about functions in SQL Server 2019. We will learn how to create functions in SQL Server and various functions with a few examples using SQL Server Management Studio.

• What is a function in SQL Server?
• Types of functions in SQL Server
• Procedures vs functions in SQL Server
• How to create a function in SQL Server
• How to create a scalar function in SQL Server
• How to create a table function in SQL Server

What is a function in SQL Server?

In SQL Server, a function is a block of SQL statements that performs a task and returns a value. A function can take some input parameters and performs a specific task to return a value.

We have a large number of SQL functions that can work on different data types and return a value. We can use functions to work on data like numeric data, string data, table data, etc.

Types of functions in SQL Server

There are mainly two types of functions in SQL Server:

• System Defined Functions: System-defined functions are already created in the database. These are provided with the SQL Server to make it easy for the user to perform some very common tasks.
• User-Defined Functions: These are the functions that are created by the user according to their specific requirements and functionalities they want to add into their SQL code.

For all the examples, we are going to use sql server 2019 and SQL server management studio.

Procedures vs functions in SQL Server

Procedures and functions seem very similar in many aspects. However, there are a lot of differences between the two. The following are the major points of differences between the two:

Functions	Procedures
A function must always return a value	A procedure may or may not return a value
A function works only with input parameters and not with output parameters	A procedure can work with input parameters as well as output parameters
We can use only the SELECT statement with the functions. This means we cannot modify the table data with the help of functions	We can use the SELECT statement as well as any other DML statements(INSERT, UPDATE, DELETE). This means we can change or modify the table data with procedures
We cannot use the feature Exception Handling inside a function	We can use Exception handling with the help of a try-catch block inside a procedure
We can use functions within a SELECT statement	We cannot use procedures within a SELECT statement
We cannot call a procedure inside a function	We can call a function inside a procedure
A function is compiled every time it is called	A procedure is compiled once and can be called multiple times.

After reading the above points of differences you might have understood how functions are different from procedures.

How to create a function in SQL Server

In this section, we will learn how to create a function in SQL Server 2019.

There are two ways in which you can create a function in SQL Server Management Studio

1. Create a new query manually and create a function.
2. Creating a function template from the SQL Server Management Studio.

Create a new query manually and create a function:

• Run the SQL Server Management Studio.
• Click on New Query as shown in the image below:

• You will see a new query window where you can write queries and execute them.
• To create a function, we use the CREATE FUNCTION statement.
• The general syntax is written below:

CREATE FUNCTION function_name(input parameters)
RETURNS return_type
AS
	SQL statements
	RETURN return_value     
GO

• In the above syntax, function_name is the name of the function you want to define.
• Inside the round brackets, you specify the input parameters along with their data types.
• return_type is the data type of the value the function will return.
• return_value is the value that the function will return.

Creating a function template from the SQL Server Management Studio:

• In the SQL Server Management Studio, you have an Object Explorer Window, where you can see your database hierarchy i.e. how objects are stored inside the database.
• Double click on any folder to see the contents inside the folder.
• You can also see your functions under:
• Databases > (Your database name) < Programmability < Functions.

• Here you can see all types of functions that you have created, whether they are Scalar Functions or Table Functions.
• You can also see System Functions.
• You can create a function with a simple right-click. For example, if you want to create a Scalar-Valued Function, right-click on Scalar-Valued Function and click on Scalar-Valued Function.

• You will get a template for Scalar-Valued Function and you can edit the template to write a function.

Let us look at an example to understand:

• Write the following query in the query tab.

CREATE FUNCTION getEmpInfo()
RETURNS TABLE
AS
	RETURN(SELECT * FROM [dbo].Employees)
GO

• This function will get all the data from the Employees table.

• Now we will call this function within a SELECT statement using the following query:

SELECT * FROM getEmpInfo()

Thus, we learned how to create a function and how to call a function in SQL Server 2019.

How to create a scalar function in SQL Server

We will learn to create a scalar function in SQL Server 2019. But, first, we should know what is a scalar function.

What is a scalar function in SQL Server? A scalar function is a user-defined function that returns only a single column value. It may or may not take some input parameters, but always returns some value.

Now, let us create a scalar function in SQL Server 2019.

• To create a scalar function, use the CREATE FUNCTION statement. The syntax is given below:

CREATE FUNCTION function_name()
RETURNS return_type
AS
	SQL statements
	RETURN return_value     
GO

• Let us create an example. The following following function will find the maximum of two numbers:

CREATE FUNCTION Findmax()
RETURNS int
AS
BEGIN
DECLARE @num int
        @num1 int
        @num2 int
SET @num1=13
SET @num2=45
IF(@num1>@num2)

	SET @num=@num1
ELSE
	SET @num=@num2
RETURN @num
END

• This function will add two integers and returns the result.
• We execute a function with the SELECT statement. We can also specify a column name for the returned value. The query will be:

SELECT [dbo].FindMax() as MaxNumber

Hope you understood how to create a scalar function in SQL Server 2019.

How to create a table function in SQL Server

In this section, we will learn how to create a table function in SQL Server 2019.

What is a table function in SQL Server? A table function in SQL Server is a function that returns table data as the return value. It may take some input parameters and gives result in the form of a table.

Let us understand table functions in sql server with the help of an example.

• We will create a table function that will return all the details of the Product table having a Price greater than 100.

CREATE FUNCTION ListWithPrice()
RETURNS TABLE
AS
RETURN
SELECT * FROM Products WHERE Price>100

• Now execute the table function.

SELECT * FROM [dbo].ListWithPrice()

This is how to create a table function in SQL Server 2019 using management studio.

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
• SQL Operand data type real is invalid for modulo operator

At the end of this tutorial, we are now aware of the functions in SQL Server. We also learned how to create different types of functions in SQL Server 2019.

• What is a function in SQL Server?
• Types of functions in SQL Server
• Procedures vs functions in SQL Server
• How to create a function in SQL Server
• How to create a scalar function in SQL Server
• How to create a table function in SQL Server