 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)ASBEGIN DECLARE @P CHAR(5) if (@NUM>90) beginSET @P='A'end else if @NUM<90  AND @num>80 beginSET @P='B'endelse if @NUM>70 AND @NUM <80beginSET @P='C'end    ELSEBEGINSET @p='D'END RETURN (select @P)END ```

Inline table-valued function:

In the above example, we have found the grade of a student, by inline valued function

we can get multiple rows just by entering a single value.

For example we need a record for the student whose grade is ‘A’.

Here is the small program to understand this concept.

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

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)ASbegininsert into @grade3select student_id,dbo.GRADE(marks)from Student_Dtgroup by student_id,name,markshaving dbo.GRADE(marks)=@entry returnend
Syntax to call the function:Select * from functionname(‘object’)```

Modified On Mar-16-2018 06:47:32 AM
1. This is a very useful post,I would like to apreciate you for this post.
Thanks