Table Content
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.
Type of function
SQL provide two type of 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.