Tuesday, 28 November 2017

Top 10 differences between Functions and Procedures in SQL

Functions are Procedures are the most common concept used in advanced or complex applications to deal with data effectively and efficiently.

Please find the difference between Functions and Procedures as below:

Procedures may or may not return a value
Functions always return a value
If Procedures returning a value, it can return one or N number of values
Functions return one and only one value
It can have parameters of type input or output
It has only input parameters
To specify the output parameters, we use output parameter
To specify output parameter, we use 'returns' clause
No need to use 'return' statement for returning the value. Assigning value to an output parameter is enough
To return a value we should use the 'return' statement
It allows Transaction management, error handling using Try-Catch and Error-Raising
None of the three are allowed
It allows call a procedure or a function from it
It allows to call functions but not procedures
A procedure cannot be embedded within a SELECT statement and executed
Function can be embedded directly under a SELECT statement and executed
To call Procedure 'EXECUTE' statement is used
To call Function 'SELECT' statement is used
It is used for defining the computer business logic
It is used for defining mathematical calculations

No comments:

Post a Comment