SQL Server Scalar Function

User Defined SQL Server Scalar Function

In this article, I am going to discuss the user-defined SQL Server Scalar Function with some examples. A function in SQL Server is a subprogram which is used to perform an action such as complex calculation and returns the result of the action as a value. There are two types of functions in SQL Server, such as

  1. System Defined Function
  2. User Defined Function

The function which is already defined by the system and ready to be used by the developer is called as system-defined function whereas if the function is defined by the user or developer then such type of functions are called as the user-defined function.

Some functions take parameters; do some processing and returning some results back

For example:

SELECT SQUARE(3)

Output: 9

Some functions may not take any parameters, for example

SELECT GETDATE()

So we can say that a function can have the parameter that is optional but a function should always return a value that is mandatory.

Types of User Defined Function:

In SQL Server, we can create three types of User Defined Functions, such as

  1. Scalar functions
  2. Inline table-valued functions
  3. Multi-statement table-valued functions

In this article, we are going to discuss the SQL Server Scalar Function in details, the rest two user-defined functions are going to be discussed in our upcoming articles.

SQL Server Scalar Functions

The functions which return only a single value (scalar value) from the table is known as scalar value function. Scalar value functions may or may not have parameters which are optional, but always return a single (scalar) value which is mandatory. The returned value which is return by the SQL Server Scalar Function can be of any data type, except text, ntext, image, cursor, and timestamp.

User Defined SQL Server Scalar Function Syntax:

User Defined SQL Server Scalar Function Syntax

The syntax for calling a Scalar Function in SQL Server:

SELECT dbo.<FunctionName>(Value)

When calling a scalar user-defined function we must supply a two-part name i.e. owner name and function name. The Dbo stands for database owner name

We can also invoke a scalar function in SQL Server using the complete three-part name i.e. database name. Owner name and function name.

Let us discuss some examples for better understanding

Example1: Create a Scalar Function in SQL Server which will return the cube of a given value.

CREATE FUNCTION SVF1(@X INT)
RETURNS INT
AS
BEGIN
  RETURN @X * @X *@X
END

To execute the above function call it like below

SELECT dbo.SVF1(5)

Example2: Write a function to get the date difference.

Let us now create a function which will calculate and returns the age of an employee. To compute the age we require date of birth. So, let’s pass date of birth as a parameter. So the user-defined CalculateAge() function returns an integer and accepts date parameter.

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

Calling the above function:

SELECT dbo.CalculateAge (’02/29/1988′)

SELECT dbo.CalculateAge (’02/29/1988′) As Age

NOTE: The User Defined SQL Server Scalar Function can also be used in the select clause of an SQL Query.

To understand the above points lets create the following Employee table and populate the table with some test data.

User Defined SQL Server Scalar Function

Please use below SQL Script to create and populate the Employee table.

-- Create Table Employee
CREATE TABLE Employee
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  DOB DATETIME
)
GO

-- Populate Employee table with some test data
INSERT INTO Employee(ID, Name, DOB) VALUES(1, 'Pranaya', '1988-02-29 21:29:16.667')
INSERT INTO Employee(ID, Name, DOB) VALUES(2, 'Kumar', '1989-03-27 21:29:16.667')
INSERT INTO Employee(ID, Name, DOB) VALUES(3, 'Rout', '1990-04-15 21:29:16.667')

Let’s see how to use the SQL Server Scalar Function (CalculateAge) in the Select clause of an SQL Query

SELECT ID, Name, DOB, dbo.CalculateAge(DOB) AS Age 
FROM Employee

OUTPUT:

User Defined SQL Server Scalar Function

Let’s see how to use the user-defined SQL Server Scalar function (CalculateAge) in the where clause of an SQL Query

Let’s find out the details of those employees whose age is greater than 28.

SELECT ID, Name, DOB, dbo.calculateAge(DOB) AS Age 
FROM Employee
WHERE dbo.calculateAge(DOB) > 28

OUTPUT:

SQL Server Scalar Function

If you want to alter the function then you need to use ALTER FUNCTION FuncationName statement and to delete the function, you need to use DROP FUNCTION FuncationName.

To view the text of the user-defined function use sp_helptext FunctionName

A stored procedure can also accept the DOB of an employee and return the age but we cannot use a stored procedure in a select clause or where clause. This is one of the differences between a function and a stored procedure. In our upcoming articles as we progress we will discuss more differences between them.

Example:
CREATE PROCEDURE spCalcualateAge(@DOB DATE)
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
  SELECT @AGE
END

--Execute
Execute spCalcualateAge '02/29/1988'

In the next article, I will discuss Inline Table-Value User Defined Function in SQL Server.

SUMMARY

In this article, I try to explain the SQL Server Scalar Function step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *