Difference Between Stored Procedure and Function

Fundamental difference between Stored procedure vs User Functions:

1. Procedure may return none or more values.Function must always return one value, either a scalar value or a table.
2. Procedure have input,output parameters.Functions have only input parameters.
3. procedures are called independently whereas Functions are called from within SQL statement.
4. Functions can be called from procedure.Procedures cannot be called from function.
5. Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
6. Transaction management possible in procedure but not in function.
7. Procedure are compiled for first time and compiled format is saved and executes compiled code when ever it is called. But function is compiled and executed every time it is called.
8. Procedures can affect the state of the database by using insert,delete,update and create operations.Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
9. Procedures can change server environment whereas Function can not change server environment.
10. Cannot JOIN a Procedure in a SELECT statement.Can JOIN a Table values UDF in a SELECT statement.

Posted in: SQL Interview