User Defined Functions in SQL Server

The functions created by the user are called user defined functions.

 Types of user defined functions:

 1. Scalar Valued Functions
2. Table Valued Functions

1. Scalar Valued Functions:

The functions which return scalar (single value) to the calling environment are called scalar valued functions.

 Syntax:

create function (function_name)
(@para1 datatype(size), ……@para n datatype (size))
retrun datatype
as
begin
function body
return ( variable)
end

For executing the function

Declare @ var (datatype)
Set @var= owner.funciton_name ( para1, para2..)
print@var

Example: Write a function to find the sum of the two numbers

Create function add_sum ( @a int, @b int)
Return int
As
Begin
Declare @c int,
Set @c = @a+@b;
return(@c);
end

Executing the function

Declare @z int
Set @z= dbo.add_sum(3,5)
print@z

Example: Write a function to find the net salary of an employ use parameter to read empno and display the net using return value.

Create function net_sal (@vno int)
Return int
As
Begin
Declare @vsal int, @vcomm int
Select @vsal=sal @vcomm=comm from emp 
where empno=@vno

 if(@vcomm is null)
Return (@vsal)
Else
Return (@sal+@vcomm)
End

Executing the function

Declare @k int
Set @k=dbo.netsal(7369)
Print @k

2. Table Valued Functions:

These functions will return entire table to the calling environment. Syntax:

.
Create function function_name
(@par1 datatype (size)
@par2 datatype (size)...@para n datatype (size))
Returns table 
As return (select statment)

Executing the function:

Select * from function_name (value1, value2, value3)

Example:

Create function dept_fun()
Return table
As return (select * from dept)

Select * from dept_fun();

Create function dept_fun(@n int)
Returns table
As
return(select * from dept where deptno=@n)

Select * from dept_fun (10)

                                                                                                  Aggregate Functions in SQL