How to CREATE FUNCTION in SQL Server

Knowing about functions is very important cause it reduces the repetitive task. I will show you how to CREATE FUNCTION in SQL Server in this SQL Server tutorial.

You will understand what function is, why to use the function, and how it reduces the repetitive task by encapsulating complex logic. Then, with syntax, you will understand how the function is created in SQL Server.

After that, with an example, you will learn how to create a function and sum two values. Then, you will learn how to create a function within the specific schema.

Finally, you will understand how to view the function in the current databases and delete it when it isn’t required.

Table of Contents

CREATE FUNCTION in SQL Server

First, ‘What is function’ is a function that contains a set of statements or operations; it is used to encapsulate complex logic. The function increases the modularity and readability of your SQL Server scripts.

You can encapsulate some operation or script code in a function and call this function where it is required. This means you can reuse the function, so here, you need to write the code one time and use it anywhere as the requirement arises. Thus, it increases the efficiency.

To create a function in SQL Server, use the syntax below.


CREATE FUNCTION [schema_name.]function_name
(
	-- Here Specify the Parameters 
	@parameter1 datatype [ = default_value ],
	@parameter2 datatype [ = default_value ],
	...
)
RETURNS return_datatype
AS
BEGIN
	-- Function logic here
	RETURN [value]
END

Where,

CREATE FUNCTION: A command to create a new function.
[schema_name.]function_name: Name the function you want to create; optionally, you can create a function within a specific schema by specifying the schema_name.function_name.
@parameter1: It is the parameters; as you know, a function can accept different parameters, and these parameters are variables used to pass the data to the function.
datatype: It is the data type of the parameters; it can be int or varchar, which means what kind of data the function will accept.
[ = default_value ]: Optionally, you can pass the default value to parameters.
RETURNS return_datatype: After the keyword RETURNS, specify the return type of the function, which means the value that the function will return.
AS BEGIN … END: This section contains all the SQL statements that represent the function logic.

Let’s take a simple example and see how to create and call the function in SQL Server.

For example, create a function named SumTwoValues, which accepts two values and returns the sum. You can use the CREATE FUNCTION statement to create a functional, as shown below.

CREATE FUNCTION SumTwoValues 
( 
	@firstValue INT, 
	@secondValue INT 
)
RETURNS INT
AS
BEGIN
     RETURN @firstValue + @secondValue;
END;

CREATE FUNCTION in SQL Server to Sum Two Values

Here, the function name is SumTwoValues, which accepts two parameters, @firstValue and @secondValue of type INT, which pass the two values you want to sum.

Then, RETURNS INT means the function will return an integer value, which is the sum of two values.

within BEGIN and END sections, the statement RETURN @firstValue + @secondValue, sum the value in a variables @firstValue and @secondValue using the plus (+) operator. Then, the sum value is returned, specified by the RETURN keyword.

When you execute the above query, it creates a function named SumTwoValues in your database. Once the function is created, you can call it with two values, as shown in the query below.

SELECT dbo.SumTwoValues(4, 10) AS SumValue;

Call a FUNCTION in SQL Server to Sum Two Values

From the output, the function returns the sum as 14. While calling the function, two values, 4 and 10, are passed to SumTwoValue() function. You can pass any two values that you want to sum.

This is how to use CREATE FUNCTION in SQL Server to create a function.

Next, let’s see how to use the function with the table.

CREATE FUNCTION in SQL Server with Specific Schema

Here, I will show you how to use the function with a table to perform some operations And create a function within the specific schema.

For example, you have a table called SalesTransaction in the Sales scheme of your database.

The table is shown below.

Creating and using Functions with SalesTransaction Table

This table exists in the Sales schema. Consider you need to compute sales tax for orders in different regions, where each region has a distinct tax rate.

To compute sales tax, you must write a set of statements repeatedly for each region, but function can simplify this repetitive task. Run the query below for that.

CREATE FUNCTION Sales.CalculateSalesTax
(
	@saleAmount DECIMAL(10,2),
	@taxRate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
	RETURN @saleAmount * @taxRate / 100
END

CREATE FUNCTION in SQL Server with Specific Schema

After executing the above query, it creates the CalculateSalesTax() function in the Sales schema of the database.

Let’s call this function to compute the sale tax for each transaction ID in the SalesTransaction table. For that, use the query below.

SELECT TransactionID,
    Sales.CalculateSalesTax(SaleAmount, TaxRate) AS SalesTax
FROM Sales.SalesTransactions;

Calling FUNCTION in SQL Server within Specific Schema

The query computed the sales tax value for each transaction, Here within the SELECT statement, calling the CalculateSalesTax function from the schema Sales.

In the query, columns SaleAmount and TaxRate are passed as values to CalculateSalesTax.

You can use this function to find the sale tax for orders in the different regions. You only have to pass the values through the column.

Next, let’s see how to view the function using the SSMS and query.

Viewing Stored Function in the Database

To view the function, use SQL Server Management Studio.

Expand your current database (e-commerce), expand the Programmability, expand the Functions folder, and then expand the Scalar-valued function, as shown in the picture below.

As you can see, the function is Sales.CalculateSalesTax(). If you want to see the function in the current database, run the query below.

SELECT
	name AS FunctionName,
	type_desc AS FunctionType
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF', 'FS', 'FT')
ORDER BY name;

As you can see, the current database contains the function CalculateSalesTax of type SQL_SCALAR_FUNCTION.

The function is stored in the sys.objects catalogue view, which contains rows representing each object created under the database. So, in the above query, fetch the object name and type_desc.

This function is scalar because it accepts one more parameter and returns a single value.

This is how to view the created query in SQL Server using the SSMS and query.

Next, let’s see how to drop or delete the created function.

Deleting Function in SQL Server

Following the syntax below, drop or delete the function from the specific schemas.

DROP FUNCTION schema_name.function_name;

Where,

DROP FUNCTION: It is the command to delete the function.
schema_name.: It is the name of the schema where your function exists.
function_name: Name of the function that you want to delete.

For example, to drop the function CalculateSalesTax, you can execute the query below.

DROP FUNCTION Sales.CalculateSalesTax;

When you execute the above query, it deletes the CalculateSalesTax from the schema Sales.

You can also delete the function using the SQL Server Management Studio.

For that, locate the function in the Functions subfolder of the Programmability folder in your database, right-click on the function, and then select ‘Delete’ from the context menu, as shown in the picture below.

Delete Function from Database in SQL Server

A new dialogue Delete objects appears when you click the ‘Delete’ option. You need to click the OK button to delete the selected function, as shown in the picture below.

Delete Function from Database in SQL Server using Delete Objects Dialog

After that, refresh your Object Explorer section; you won’t see the function CalcualteSalesTax.

This is how to delete the function in the SQL Server from the current database.

Conclusion

You learned how to create functions in SQL Server using the CREATE FUNCTION statement. You also learned how to create a function that accepts a value and returns a single, called a scalar function.

Additionally, you created a function in a particular schema, viewed the stored function through SSMS and query, and then deleted the function.