To complete a task, database objects like the stored procedures and Functions contain a set of SQL statements.
Function: Function is compiled and executed every time it is called. This cannot modify the data received as parameters and function must return a value.
Have a look at this video to understand the FUNCTION in detail.
Stored Procedure: This is a pre-compiled object as this gets compiled for the first time and its compiled format gets saved as well, which gets executed (compiled code) when it is called.
Refer to this informative video about STORED PROCEDURE
Basic differences between Function and Stored Procedure are as follows:
The function always returns a value.
Stored Procedure will not return a value, but the procedure can return “0” or n values.
Functions have only input parameters for it.
Whereas, Procedures can have output or input parameters.
You can call Functions can be from Procedure.
But the vice-versa is not correct. As you can’t call Procedures from a Function.
Want to learn SQL from basics! Here's the right video for you on SQL provided by Intellipaat:
In SQL Server advance differences between Stored Procedure and Functions are as follows:
You can use Transactions in Procedure.
But, you can’t Transactions in Function.
By using a try-catch block, an exception can be handled in a Procedure.
Whereas, you can’t use try-catch block in a Function to handle the exception.
You can’t utilize Procedures in a SELECT statement.
But Function can be utilized in a SELECT statement.
The procedure allows as DML(INSERT/UPDATE/DELETE) as well as a SELECT statement in it.
Whereas, Function allows only SELECT statement in it.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT statement.
The function can be used in the SQL statements anywhere in SELECT/WHERE/HAVING syntax.