User Defined Function in SQL

In this blog we learn basic functions of SQL.

Table of Contents

• Introduction
• Why we use function
• Type of function
• System defined function
• User defined function

Introduction

We all are from a programming background and we know what function is and why we need it. But I am going to recap what function is. Function provides us with encapsulated reusable logic. In other words function hides the steps and complexity of code and we use that function where we want.



Why we use function

There are benefits of using function. Some of them are following.

• We can use user defined function in select as well as where class.
• We can use user defined function used as rowset.
• We can use user defined function as parameterize views.

Type of function

SQL provide two type of functions.

• System defined function
• User defined function

System defined function

System defined functions are provided by SQL itself. You can find the details of these functions on the following. For more details click here.

User defined function

User defined functions are two types. First is Scalar and second is Table value.

Scalar Function

These functions return only a single value. These functions are any data type;  it could be return int , varchar or datetime. Let’s understand by example. Let’s create a table and name it myTable.

CREATE TABLE [dbo].[myTable](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [myDate] [date] NOT NULL,
   [data] [int] NOT NULL
)

 

Let’s insert some records into it.

INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-01-01’, 10)
INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-02-01’, 15)
INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-03-01’, 20)
INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-04-01’, 15)

Let’s run a select statement to check if it’s working or not.

select * from myTable

Output



Let’s create a scalar function. This function will do some calculations. This function performs some calculations like it checks the date on the behalf of id and if that date contains the first month of the year it returns 0 else it performs this formula.

Change value = (current month data / previous month data) * 100

Create Function dbo.MyFunction(@id int)
returns decimal
as
begin
declare @currentMonth int
declare @currentYear int
declare @returnValue float
declare @currentMonthData decimal
declare @previousMonthData decimal
set @currentMonth = (select MONTH(myDate) from myTable where id=@id)
set @currentYear = (select Year(myDate) from myTable where id=@id)
if @currentMonth=1
begin
set @returnValue=0
end
else
begin
set @currentMonthData = (select data from myTable where id=@id)
set @previousMonthData = (select data from myTable where month(myDate)=@currentMonth-1 and Year(myDate)=@currentYear)
set @returnValue = (@currentMonthData/@previousMonthData)*100
end
return cast(@returnValue as decimal(10,2))
end
-- Call that function
select *,dbo.MyFunction(id) as 'Quote change' from myTable

Output



Table value function

As the  name suggests it returns a table. A table valued function can be used anywhere a table can be used like in select statement or in procedure or view.

// Normal table value function
Create Function GetDetail()
returns table
as
return (select * from myTable)
// Parameterize table value function
Create Function GetDetailById(@id int)
returns table
as
return (select * from myTable where id=@id)
select * from GetDetail()

Output

select * from GetDetailById(1)

Output



I hope this blog was useful to you.