Function Vs Stored Procedure In SQL

In order to perform a task, data objects like stored procedures and functions are used in SQL. These data objects usually consists of the set of SQL statements.

Function: A function is a set of statements which is compiled and executed every time it is called. Data modification cannot take place and a function must return a value.

Stored procedures: Stored procedure is referred to as a pre-compiled object as this gets compiled for the first time and the compiled format will be saved as well, which will be executed as and when required whenever it is called.

In SQL, a function does not use transactions while the stored procedures do use the transactions. An exception can be handled by using a try-catch block in a procedure, however, it cannot be handled when used in a function. A function can be used in the SELECT statement while a stored procedure cannot be utilized in the SELECT statement. A function allows only a SELECT statement in it while  stored procedure allows all the DML statements that include INSERT, UPDATE, DELETE.