There are three Types of User Defined Functions in Sql Server:

·         Scalar

·         Inline Table-Valued

·         Multi-statement Table-Valued

Scalar User Defined Functions

The scalar user defined functions can accept zero to many parameters and will return a single scalar value. A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types.

Example

CREATE FUNCTION AddTwoNumbers
(
@num1 int,
@num2 int
)
RETURNS int
AS
BEGIN
RETURN @num1 + @num2
END

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

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 table
AS
RETURN(SELECT * FROM EMP where DEPT=@dept)
GO
The 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

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)
)
AS
BEGIN
INSERT INTO @DEPT SELECT  EMPID,DEPT FROM EMP WHERE EMPID = @id
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @DEPT VALUES ('','No Authors Found')
END
RETURN
END
GO

The above function GetDeptByIDwill accept one parameter @id and gives

the output in table DEPT.

Call the function using this statement

SELECT * FROM GetDeptByID(2)

Output