User defined function

1. scalar function
2. inline table valued function
3. Multi-statement table-value function

Scalor user defined function in sql server:

create function function_name(@p1 data_type,@p2 datatype,@p3 data_type)

returns return_datatype

begin

----function body

return return_datatype

end

Scalor function have may or may not parameter,but always return a single

value.the returned value can be of any data type,except text,image,cursor

and timestamp.

executing an scalor defined function

select dbo.function_name(parameter)

example of function

create function cal_age(@dob date)

returns int

begin

--declare @dob date

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

select dbo.cal_age('09/01/1993')

Inline Table valued function

Returns a table

· Specified the table as returned type, instead of any scalar type. The function body is not enclosed between begin and end. the structure of table that gets returned, is determine by select statement in function

create function functionname(parameter)

returns table

return(select statement)

how to call this function

select * from function_name(' ')

use of inline table value function

1. It can be used to achieved the functionality of parameterized view.
2. The table returned by table valued function, can also be used with join with other table.

Multi-statement table valued function

It is very similar to inline table valued function but there is only few difference.

**In inline table valued function return can't contain the structure of table.but in multi-statement table valued function return can contain the structure of table.

**ist one doesn't have begin and end clause.

but 2nd have this.

**inline table value function it's possible to update underlying

Table but not possible in multi value function.

ex:-update fun_name set column=' '

Note:-internally sql server treats an inline table valued function as view and multiline statement function as store procedure.

Example of inline and multiline statement

create function in_fun(@gender nvarchar(20))

returns table

return(select * from employee where gender=@gender)

select * from in_fun('male')

Multi statement value table function

CREATE FUNCTION GetAvg_2(@Name varchar(50))

RETURNS @Marks TABLE

(Name VARCHAR(50),

Subject1 INT,

Subject2 INT,

Subject3 INT,

Average DECIMAL(4,2)

)

BEGIN

DECLARE @Avg DECIMAL(4,2)

DECLARE @Rno INT

INSERT INTO @Marks (Name)VALUES(@Name)

SELECT @Rno=roll FROM Student WHERE Name=@Name

SELECT @Avg=(Subject1+Subject2+Subject3)/3 FROM Student WHERE roll=@Rno

UPDATE @Marks SET

Subject1=(SELECT Subject1 FROM Student WHERE roll=@Rno),

Subject2=(SELECT Subject2 FROM Student WHERE roll=@Rno),

Subject3=(SELECT Subject3 FROM Student WHERE roll=@Rno),

Average=@Avg

WHERE Name=@Name

RETURN

END

SELECT * FROM GetAvg_2('amit')

Important concept related to function in sql server

Deterministic function: - Always return same result any time they are called

With specific set of input value and given same set of data set.

example:-square(),power(),sum(),avg(),count()

all aggregate are deterministic function.

Non deterministic function:- may return diffrent result set each time. When they are called specific set of value.

Example:-getdate() and current timestamp

Rand() function:- it is a non deterministic function but if we provide seed value

Function become deterministic.

SQL Server