Table of Contents

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.

sql function

Why we use function

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

Type of function

SQL provide two type of functions.

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.

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

Let’s insert some records into it.
  1. INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-01-01’, 10)
  2. INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-02-01’, 15)
  3. INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-03-01’, 20)
  4. INSERT [dbo].[myTable] ( [myDate], [data]) VALUES (‘2015-04-01’, 15)
Let’s run a select statement to check if it’s working or not.
  1. select * from myTable
Output

see 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
  1. Create Function dbo.MyFunction(@id int)
  2. returns decimal
  3. as
  4. begin
  5. declare @currentMonth int
  6. declare @currentYear int
  7. declare @returnValue float
  8. declare @currentMonthData decimal
  9. declare @previousMonthData decimal
  10. set @currentMonth = (select MONTH(myDate) from myTable where id=@id)
  11. set @currentYear = (select Year(myDate) from myTable where id=@id)
  12. if @currentMonth=1
  13. begin
  14. set @returnValue=0
  15. end
  16. else
  17. begin
  18. set @currentMonthData = (select data from myTable where id=@id)
  19. set @previousMonthData = (select data from myTable where month(myDate)=@currentMonth-1 and Year(myDate)=@currentYear)
  20. set @returnValue = (@currentMonthData/@previousMonthData)*100
  21. end
  22. return cast(@returnValue as decimal(10,2))
  23. end
  24. -- Call that function
  25. select *,dbo.MyFunction(id) as 'Quote change' from myTable
Output

run

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.
  1. // Normal table value function
  2. Create Function GetDetail()
  3. returns table
  4. as
  5. return (select * from myTable)
  6. // Parameterize table value function
  7. Create Function GetDetailById(@id int)
  8. returns table
  9. as
  10. return (select * from myTable where id=@id)
  11. select * from GetDetail()
Output

result
  1. select * from GetDetailById(1)
Output

Output

I hope this blog was useful to you.