Stored Procedure and User Defined Functions both are SQL Server objects but both are much different in implementation and usage.
- Function must return something in from of single value or table but stored procedure may or may not.
- Function cannot have output parameter where stored procedure can have output parameter.
- Function must be written with single input parameter where stored procedure can be written without any parameter.
- Functions are usually called by a Stored Procedure where Stored Procedure cannot be call from a Function.
- Function cannot have try-catch block where Stored Procedure can have try-catch block and can handle exceptions.
- Transaction block cannot be written in Function where in Stored Procedure it can be written.
Usage Difference -
- To avoid a repeating code in Stored Procedure you can write a user defined function and can use this function for all stored procedures.
- To implement a generic code can be written a function. For an example - Write a function which returns customer's Age in years base on his Date of Birth.
- Functions used usually for returning something somewhat common. For an example - In-built SQL Function getdate() returns always today date and repeatedly called in Stored Procedure.
