What is Function in SQL

What is function?

• function is a database object of sql server
• function is a collection of sql statement.
• function has only input parameter (not specify output as in procedure)
• function only return single value
• any DML statement are not allow in function 
• function can call from another function and also call from stored procedure.
• exception handling is not possible with function

Types of function in sql server

• In SQL there are two type of function are available

1. Scalar Function
• Scalar function are return only single value as result of function
• function can return any type of data as result 
2. Table value function 
• table value function can return data in table format as result of function

Example of Scalar function

Create function fnGetEmpAge

(

@EmplId INT

)

returns INT

As

Begin 

return (Select Age from TblUserMaster where EmplId=EmplId);

end 

Example of Table value function 

Create function GetAllUser()

returns Table

As

return (Select FName, LName, Age from TblUserMaster) 

End