FUNCTION IN SQL SERVER 2008R2

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).

Types of Function

1.  System Defined Function

These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

1. Scalar Function

Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.

System Scalar Function

rand(10)

This will generate random number of 10 characters

System Aggregate Function

max()

This returns maximum value from a collection of values.

min()

This returns minimum value from a collection of values.

avg()

This returns average of all values in a collection.

1.                        2. User Defined Function

These functions are created by user in system database or in user defined database. We three types of user defined functions.

2.1.  Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

-----------create first example----------

create function AddtwoNumber(@a int,@b int)  

 Returns int  

as 

begin 

return @a+@b 

 end  

-----invoke function------

 print dbfunction.dbo.addtwonumber(10,20)  

 select dbo.addtwonumber(10,30)

------------second example-------
--Create function to get emp full name----- 

Create function Fnfullname(@name varchar(50),@last varchar(50))

returns varchar(150)

as

begin

return(select @name+','+@last)

end

----invoke function----

 select dbo.fnfullname('Asha','Yadav')as Name
-----   To view the text help of function------

  sp_helptext addtwonumber
------Store Procedure-------
 Create proc Spfullname(@name varchar(50),@last varchar(50))
as
begin
select @name+''+@last
end
----execute store procedure-----
execute Spfullname 'sunil','kumar' 

-------Create Scalar User Define Function---------

create Function Age(@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

     ------invoke function------

      select dbo.Age('10/8/1991') as age

2.2.   Inline Table-Valued Function

User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

1  Create Function ingender(@gender nvarchar(10))

2     RETURNS table

3    as

     RETURN(select * from tablefunction where Gender=@gender)

----------------Calling a Inline Table Valued Function------------------

select * from ingender('male')

update ingender('male') set Name='ravi' where id=1

 

2.3  Multi-Statement Table-Valued Function

User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

------------------------multiple statement function-----------

Create Function FnMulti()

Returns @emp table(id int ,Name varchar(50),Dob varchar(50),gender varchar(50))

as

begin

insert @emp

select * from tablefunction

Update @emp set Name='Naveen' where id=1;

return

End

----invoke function----

select * from fnmulti()

select * from tablefunction

-------------------Result------------------

-------Diffrence B/w Inline and Multistatment table valued function--------------

create function intvf()

RETURNS table

as

RETURN(select Name,CAST(Dob as date) as DateOFBirth from tablefunction)

------------------------

Create function mstvf()

Returns @table table(id int,name varchar(50),DOB date)

as

begin

insert into @table

select id, name,CAST(dob as date) as DOB from tablefunction

return

end

------------calling the Inline table valued Function----------

select * from intvf()

------------calling the Inline table valued Function----------

select * from mstvf('1')

Create Function mtvfgender(@gender nvarchar(10))

 RETURNS table

 as

 Begin

 select * from tablefunction where Gender=@gender

RETURN

end