In this blog, we will discuss the difference between Stored Procedure and Function. Many functionalities are different from each other. Firstly I need to describe the Stored Procedure and function in brief.
Stored Procedure
Stored Procedures are used to perform one or more than one DML operation on the Database. Stored Procedure is the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not returns a value.
Syntax:
CREATE PROCEDURE [Your_Procedure_Name]([Parameters]) IS [Variables]; BEGIN //[Your_SQL_Statements] END;
Function
A function is a set of SQL statements that perform a specific task. If you have to write large SQL scripts repeatedly to perform the same task, you can create a function that performs that task. A function accepts inputs in the form of parameters and returns a value.
Syntax:
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Difference
| Function | Stored Procedure |
| Must return a value. | It May or may not return values. |
| Allow only Select statements, Don’t allow to use of DML statements. | It can have select statements as well as DML statements like insert, update, delete, and etc. |
| Allow only input parameters, Doesn’t support output parameters | It can have both input and output parameters. |
| Don’t allow to use of try-catch blocks. | We can use try-catch blocks for exception handling. |
| Transactions are not allowed. | We can use transactions in this. |
| Allow only table variables, Don’t allow to use of temporary tables. | We can use both table variables as well as a temporary table in this. |
| Functions can’t call the Stored Procedure. | Stored Procedures can call functions. |
| The select statement can call the Functions. | Select, Where, Having, and etc. can’t call the Stored Procedure. Execute/Exec statement used to call/execute Stored Procedure. |
| User-Defined Function can be used in join clause as a result set. | It can’t be used in the join clause. |
