---
title: "User Defined function in SQL"  
description: "In this blog, we are explaining about the basic of user defined function and difference between Inline table-valued function and Multi Statement Table"  
author: "Abhishek Srivasatava"  
published: 2016-10-04  
updated: 2018-03-16  
canonical: https://www.mindstick.com/blog/11214/user-defined-function-in-sql  
category: "mssql server"  
tags: ["sql", "sql server 2008"]  
reading_time: 2 minutes  

---

# User Defined function in SQL

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

## [Scalar function](https://www.mindstick.com/forum/34598/how-to-create-scalar-function-in-sql)

In this type of function single [return value](https://www.mindstick.com/forum/33675/how-to-convert-return-value-of-abmulivaluecopylabelatindex-into-nsstring-in-ios) will achieved. It is similar to use

Count, avg etc system defined function.

E.g.: creating a [function to calculate](https://www.mindstick.com/forum/158909/how-do-you-use-the-avg-function-to-calculate-the-average-value-of-a-column-in-sql) 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](https://www.mindstick.com/articles/157138/student-loan-default-wage-garnishment-and-student-loan), by inline valued function

we can get [multiple rows](https://www.mindstick.com/forum/159373/how-to-bulk-update-multiple-rows-in-sql-server-at-a-time-with-different-matching-conditions) 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
GRADE1(@entry char(3))RETURNS table
AS returnselect student_id,name,marks,dbo.grade(marks)as grade1from Student_Dtgroup by
student_id,name,markshaving dbo.grade(marks)=@entrygo

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](https://www.mindstick.com/blog/11188/user-define-function-in-sql-server)? 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’)
```

---

Original Source: https://www.mindstick.com/blog/11214/user-defined-function-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.