User Defined Scalar Function in SQL Server
In this article, I am going to discuss the user-defined Scalar Function in SQL Server with examples. Please read our previous article, where we discussed Stored Procedure in SQL Server. At the end of this article, you will understand what is a Scalar function in SQL Server and how to create and use SQL Server Scalar function with examples.
What is a function in SQL Server?
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
- System Defined Function
- 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)
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
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 Scalar Function Syntax:
The syntax for calling a Function in SQL Server:
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 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
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
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.
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
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
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.
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 am going to discuss Inline Table-Value User Defined Function in SQL Server. Here, in this article, I try to explain the Scalar Function 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.