Difference between function and stored procedure

Function

Stored Procedure

Function should always return a value

Store procedure may or may not return any value.

Functions can be used in select/Where/Having SQL statements

Procedures can’t be used in select/Where/Having, it can be executed and the result can be store in table then that table can be used in joins

Functions do not allow any DML on the permanent table/ temporary table (you can insert /update/delete on the table variable)

In procedures DML can be done on any of the permanent table / table variable/ temporary table

It can have only input parameters

It can have input as well as output parameter

Table can’t be passed as the input parameter in the function

Table can be send as the input parameter into the function

Can return only single table from the function

Stored procedure can be used to return multiple tables in single execution

Exception handling( try /catch) block can’t be added in the function

Exception handling is done in the procedure

Transaction can’t be maintained in the function

Transaction maintenance is allowed in the procedure

Use of temporary table is not allowed

We can use local temporary/global temporary table in the stored procedure

Function can be used in the joining ( can be joined with the table)

Stored procedures can’t be used in joins. I can only be executed

Other functions can be called in the function.

But is can’t call a procedure inside the function

Procedure can call any function/ stored procedure with itself.