There are three ways by which we can create a user defined function:

Scalar function

In this type of function single return value will achieved. It is similar to use

Count, avg etc system defined function.

E.g.: creating a function to calculate grade of a students.

Here is the small program which may help you to understand the basic of

this type:

CREATE FUNCTION GRADE
(
@NUM INT
)
RETURNS CHAR(5)
AS
BEGIN
 
DECLARE @P CHAR(5)
 
if (@NUM>90) 
begin
SET @P='A'
end
 
else if @NUM<90  AND @num>80 
begin
SET @P='B'
end
else if @NUM>70 AND @NUM <80
begin
SET @P='C'
end    
ELSE
BEGIN
SET @p='D'
END
 
RETURN (select @P)
END
 

Inline table-valued function:

CREATE FUNCTION GRADE1
(
@entry char(3)
)
RETURNS table AS
 
return
select student_id,name,marks,dbo.grade(marks)as grade1
from Student_Dt
group by student_id,name,marks
having dbo.grade(marks)=@entry
go Syntax to call the function: 
Select * from functionname(‘object’) 

Multi Statement Table-Valued Function:

Result is same when we compare to Inline table-valued function. Then why we need to use this form of values user defined function? Inline table-valued function can be simply designed but when program is large enough and time consumed by the program is considerable then Inline table-valued function is not used because it will take very much time to perform it’s operation. Whereas multi-statement Table-Valued Function is 10 times faster than Inline table-valued function. Hence as a good programmer one should always use Multi Statement Table-Valued Function and avoid using Inline table-valued function. 

Here is the small program which may help you to understand the basic of this type:

Create FUNCTION GRADE3
(
@entry char(3)
)
RETURNS @grade3 table 
(
stdid int,
grade char
)AS
begin
insert into @grade3
select student_id,dbo.GRADE(marks)
from Student_Dt
group by student_id,name,marks
having dbo.GRADE(marks)=@entry
 
return
end 
Syntax to call the function:
Select * from functionname(‘object’)