DIFFERENCE BETWEEN STORED PROCEDURE AND FUNCTION IN SQL WITH EXAMPLE
PROCEDURE | FUNCTION |
Procedure can have both input\output parameters. | But function can have only input parameter. |
Inside procedure we can use DML (INSERT/UPDATE/DELETE) statements. | But Inside function we can’t use DML statements. |
We can’t utilize stored procedure in Select Statement. | But we can use function in Select Statement. |
We can use Try-Catch Block in Stored Procedure. | But Inside function we can’t use Try-Catch block. |
Procedure can’t be call inside a function. | But we can call function inside a Procedure. |
Procedure can return 0 or n values (max 1024). | But function can return only 1 value which is mandatory. |
We can go for transaction management in procedure. | But we can’t go in function. |
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section. |
But we can use Function anywhere. |
We can’t join Stored Procedure. | But We can join functions. |