What are the difference between Stored procedure and function in sql server

Difference between Stored procedure and function

• Return Value

• Function must return any value
• Procedure can return single value or n number of rows or null

• Parameter Type

• Function has input parameter only
• Stored procedure can have input or output or both parameter

• Call

• Function can call from stored procedure
• Stored procedure can not call from function

• Use of DML

• Function can not use any DML statement
• Stored procedure can use all DML statement

• Exception handling

• Exception handling is not possible with function
• Stored procedure allow to use Exception handling

• Use of Transaction

• Function can not use any command of transaction control like begin, commit, rollback
• Transaction can be begin, commit and rollback in procedure.

• Compile

• Function compiles every time when its call
• Stored procedure compiles only once when it created