Table Content

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

Introduction

We all are from programming background and we know that what is function and why we need it. But I am going to recall that what function is. Function provide us encapsulate reusable logic. In other words function hides the step 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 parameterise views.

Type of function

SQL provide two type of function.

• System defined function
• User defined function

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

User defined function
User defined function are two type. First is Scalar and second is Table value.

Scalar Function
These functions return only single value. These functions any data type like 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 record 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 it’s working or not.

select * from myTable

Let’s create a scalar function. This function will do some calculation. This function perform some calculation like it check the date on the behalf of id and if that date contain first month of the year the it returns 0 else it perform 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

Ouptput

Table value function

As name suggested it return a table. A table valued function can be used any where 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 article useful to you.