Scalar Functions In SQL Server

In this article, we will learn about the concept of scalar functions in SQL Server.

In SQL Server, There are two types of User-defined functions:

1. Scalar Functions
2. Table-Valued Functions

So here, We will discuss the Scalar Function in detail.

Scalar Function

• Scalar function in SQL Server will always accept single or multiple parameters and return a single value. 
• The scalar functions are used make simplify our code.
• Suppose we might have complex calculations that appear in a number of queries. In this type of case, We can build a scalar function that encapsulates the formula and uses it in each query instead of writing formulas for each query.

There are some key points that need to be kept in mind at the time of creating function,

• A function must have a proper name, and the name should not begin with a special character such as @, $, #, or other similar characters.
• A SELECT statement that can only operate with a function.
• We can use a function anywhere we want like an inbuilt function.
• Whenever a function will be called, every time it will compile.
• The function must return a value.
• The function can only accept input parameters.
• We cannot be used the TRY CATCH block in the function

Syntax:

CREATE FUNCTION [schema_name].function_name (parameter_list)  
RETURNS data_type 
AS  
BEGIN  
    //statements write here  
      RETURN value  
END   

Here,

Schema_name: It will define the schema name that the function belongs to which schema name. It’s an Optional. If we don’t write a schema_name, it will take “dbo” as the default schema. 

function_name: it will define the name of the Function. 

Parameter_list: Here, we need to give the parameter with the specific data type for use in the function.

RETURNS data_type: The RETURN statement defines the return type of function.

RETURN value: The RETURN value statement defines the value that needs to be returned.

Example:

So here, we will see the example of calculating a simple Interest Calculation.

CREATE FUNCTION fn_Calculate_Simple_Interest(
@PrincipleAmount decimal(8,2),
@Time int,
@Rate decimal(5,2)
)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @SimpleInterest decimal(10,2);

set @SimpleInterest=(@PrincipleAmount*@Time*@Rate)/100

RETURN @SimpleInterest
END;

We can call the scalar function the same as the built-in function.

Write the below statement to call a function,

SELECT [schema_name.]function_name;

Here, For calling the above-created scalar function write the below statement,

SELECT dbo.fn_Calculate_Simple_Interest(1000,5,10) as Simple_Interest

After execution, it will give the output as below,

SQL Server also allows us to Modify the Scalar function by using the ALTER statement.

Here, We will see the example of an ALTER statement below,

ALTER FUNCTION fn_Calculate_Simple_Interest(
@PrincipleAmount decimal(8,2),
@Time int,
@Rate decimal(5,2)
)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @SimpleInterest decimal(10,2);

set @SimpleInterest=(@PrincipleAmount*@Time*@Rate)/100

RETURN @SimpleInterest
END;

As we can see above example, We only need to write the ALTER Statement instead of the CREATE Statement for Modify scalar function.

For the remove or delete a scalar function we need to write a DROP statement with the Function name as below,

DROP FUNCTION [schema_name.]function_name;

To remove the above-created scalar function write the below statement,

DROP FUNCTION dbo.fn_Calculate_Simple_Interest;

Advantages

• A User Defined Function can be used with SELECT, WHERE, or in the CASE statement.
• A User-Defined Function can be used to create a join.
• The User-Defines Function is simpler to call from another SQL statement.
• A User-Defined Function Output Can Be Used as a Rowset.

Disadvantages

• The user defines function cannot be used to modify the base table.
• A User-Defined Function Returns Only One Result Set.
• We can not Call Stored Procedures from a User-Defined Function.
• We can not use the dynamic SQL statement in the User-Defined Function.
• We can not return XML result data from the User-Defined Function.
• We can not use the temporary table in the User-Defined Function.

I hope this article will help you to understand the concept of the Scalar function in the SQL server.

Thank you.