---
title: "User Defined Function in SQL Server"  
description: "In this blog, I’m explaining user defined functions in sql server.  There are three Types of User Defined Functions in Sql Server:ScalarInline Table-V"  
author: "Sumit Kesarwani"  
published: 2013-05-29  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/520/user-defined-function-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# User Defined Function in SQL Server

In this blog, I’m explaining [user defined functions](https://www.mindstick.com/blog/11142/functions-in-php) in [sql server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server).\

There are three Types of User Defined [Functions in Sql](https://www.mindstick.com/forum/33707/what-is-the-difference-between-rank-and-dense_rank-functions-in-sql-server) Server:

· Scalar

· Inline Table-Valued

· Multi-statement Table-Valued

Scalar User Defined Functions

The scalar user defined functions can accept zero to many [parameters](https://www.mindstick.com/articles/87/passing-parameters-in-c-sharp) and will return a single scalar value. A Scalar user-[defined function](https://www.mindstick.com/interview/2294/what-is-the-difference-between-a-user-defined-function-and-a-stored-procedure) returns one of the scalar (int, char, [varchar](https://www.mindstick.com/forum/161878/what-is-the-difference-between-nvarchar-and-varchar) etc) data types.

##### Example

```
CREATE FUNCTION AddTwoNumbers(@num1 int,@num2 int)RETURNS intASBEGINRETURN @num1 + @num2END
```

The above function AddTwoNumbers will accept two numbers, add them and give the result.

You can use either of the two statements to call the fumtion.

```
PRINT dbo.AddTwoNumbers(10,20)SELECT dbo.AddTwoNumbers(30,20)
```

##### Output

![User Defined Function in SQL Server](https://www.mindstick.com/blogs/f1732f0f-dc97-4e19-a948-779e45611a0d/images/853b8a80-55f6-4593-ac3a-d7e4ea2e6a98.jpg)

##

##### Inline Table-Valued User Defined Function

##

##### An inline table-valued function returns a variable of data type table whose

##### value is derived from a single SELECT statement. Since the return value is

##### derived from the SELECT statement, there is no BEGIN/END block needed

##### in the CREATE FUNCTION statement.

##### Example

##

## ```
CREATE FUNCTION GetEMPByDept(@dept varchar(10))RETURNS tableASRETURN(SELECT * FROM EMP where DEPT=@dept)GOThe above function GetEMPByDept will accept one parameter @dept and gives the output in tabular format.
```

##

##

##### Call the function using this statement

```
SELECT * FROM GetEMPByDept('Testing')
```

##### Output

![User Defined Function in SQL Server](https://www.mindstick.com/blogs/f1732f0f-dc97-4e19-a948-779e45611a0d/images/0b0b6825-d2a9-4745-8896-f48feba968e7.jpg)

##

##### Multi-Statement Table-Valued User Defined Function

##

##### A Multi-Statement Table-Valued user-defined function returns a table. It

##### can have one or more than one T-Sql statement. Within the create function

##### command you must define the table structure that is being returned.

##### Example

## ```
CREATE FUNCTION GetDeptByID( @id int )RETURNS@DEPT table (ID int,DEPT Varchar(20))ASBEGININSERT INTO @DEPT SELECT  EMPID,DEPT FROM EMP WHERE EMPID = @idIF @@ROWCOUNT = 0BEGININSERT INTO @DEPT VALUES ('','No Authors Found')ENDRETURNENDGO
```

##### The above function GetDeptByID will accept one parameter @id and gives

##### \

##### the output in table DEPT.

##### \
Call the function using this statement

##### \

```
SELECT * FROM GetDeptByID(2)
```

##### Output

##### \

![User Defined Function in SQL Server](https://www.mindstick.com/blogs/f1732f0f-dc97-4e19-a948-779e45611a0d/images/65edc7b7-d948-4a5e-a62f-4d3cef62f878.jpg)

##

##

##

##

---

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

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