Exploring Scalar-Valued Functions in SQL Server: A Detailed Guide

Scalar functions may or may not have parameters, but always return a single (scalar) value. The returned value can be of any data type, except text, ntext, image, cursor, and timestamp.

To create a function, we use the following syntax:

Syntax:

CREATE FUNCTION Function_Name(@Parameter1 DataType, @Parameter2 DataType,..@Parametern Datatype)
RETURNS Return_Datatype
AS
BEGIN
    Function Body
    Return Return_Datatype
END

Code: Use this below code to create a function

/* Scalar Valued Function 1 */
CREATE FUNCTION CubeFunction(@X INT)
RETURNS INT
AS
BEGIN
  RETURN @X * @X *@X
END
-- Execute
SELECT dbo.CubeFunction(5) AS Cube

Code: Example of Scalar Valued function

/* Scalar Valued Function 2 */

 CREATE FUNCTION Addition(@Num1 Decimal(7,2),  
                         @Num2 Decimal(7,2))  
RETURNS Decimal(7,2)  
Begin  
    DECLARE @Result Decimal(7,2)  
    SET @Result = @Num1 + @Num2  
    RETURN @Result  
end  

-- Execute 
print dbo.Addition(12,13) 

Code: Example of Scalar Valued function

/* Scalar Valued Function 3 */
CREATE FUNCTION CalculateAge
(
  @DOB DATE
)
RETURNS INT
AS
BEGIN
  DECLARE @AGE INT
  SET @AGE = DATEDIFF(YEAR, @DOB, GETDATE())-
  CASE
    WHEN (MONTH(@DOB) > MONTH(GETDATE())) OR
       (MONTH(@DOB) = MONTH(GETDATE()) AND
        DAY(@DOB) > DAY(GETDATE()))
    THEN 1
    ELSE 0
  END
  RETURN @AGE
END

-- Execute
SELECT dbo.CalculateAge('12-02-1996') as age


-- Use the above function in a Query
SELECT Emp_ID, Emp_Name, Emp_DOB, dbo.CalculateAge(Emp_DOB) AS Age 
FROM Emp_Master

Code: Example of Scalar Valued function

 /* Drop Function */
 DROP FUNCTION FuncationName

 

A stored procedure also can accept DateOfBirth and return Age, but you cannot use stored procedures in a select or where clause. This is just one difference between a function and a stored procedure. There are several other differences, which we will talk about in a later session.