Scalar Function in SQL Server

Pre-requisites: Categories of SQL Functions

In SQL Server, a scalar function is a type of user-defined function that returns a single scalar value based on the input parameters passed to it. The scalar function is used to perform some calculations or operations on the input parameters and return a single result.

Scalar functions are commonly used in SQL Server to simplify complex queries and to encapsulate business logic. They can be used in queries just like any other SQL function, and their return value can be used as a column value, a part of a where clause, or in any other expression.

Scalar functions can be created in SQL Server using the CREATE FUNCTION statement. They can be either deterministic or non-deterministic. 

Deterministic functions always return the same result for a given set of input parameters, while non-deterministic functions may return different results for the same input parameters.

Syntax :

For Creating Function:

CREATE FUNCTION  FUNCTION_NAME
(@Parameter1 DATATYPE,@Parameter2 
DATATYPE,@Parameter3 DATATYPE,....,
@ParameterN DATATYPE)
RETURNS Return_Datatype
AS
BEGIN
  --Function Body
    RETURN Return_Datatype
END

For Updating the function :

ALTER FUNCTION  FUNCTION_NAME(@Parameter1
 DATATYPE,@Parameter2 DATATYPE,
 @Parameter3 DATATYPE,....,
 @ParameterN DATATYPE)
RETURNS Return_Datatype
AS
BEGIN
 --Function Body
   RETURN Return_Datatype
END

For Deleting the Function:

DELETE FUNCTION Function_Name()

Here’s an example of a simple scalar function in SQL Server:

Step 1: First create the table ‘Products’:  

create table Products
(productID int primary key,
ProductName varchar(50),
price decimal ,
Quantity int)

insert into Products(productID,
ProductName,price,Quantity)
Values(1,'Chai',40,20),
(2,'Biscut',5.50,200),
(3,'Rust',10,150),
(4,'sugar',24.50,20),
(5,'Coffee',78.20,10)

select * from Products

Output:

Output

Step 2: Create Function 

--Create function
CREATE FUNCTION CalculateTotal
(@Price decimal(10,2),@Quantity int)
RETURNS decimal(10,2)
AS
BEGIN
   RETURN @Price * @Quantity
END

Output:

 

 The CalculateTotal function is a simple scalar function that takes two input parameters, @Price and @Quantity, and returns their product as a single scalar value

Step 3: Call the Function using dbo.CalculateTotal( ):

SELECT ProductName,Quantity,price, 
dbo.CalculateTotal(Price, Quantity) 
AS Total
FROM Products

Output:

 

In this example, The CalculateTotal function is used to calculate the total price of each product in the Products table, and the result is displayed in a column named Total.

Hence the scalar function is useful for returning the single scalar value and also functions can be used with select and where clauses also, which makes functions more effective and useful.