Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Difference between Function and Procedure
SQL (Structured Query Language) is a computer language which is used to interact with an RDBMS (Relational Database Management System). It is basically a method of managing, organizing, and retrieving data from a relational database. In SQL, two important concepts are used namely, function and procedure.
A function calculates the results of a program based on the inputs provided, whereas a procedure is used to perform some tasks in a specific order. There are many other differences between functions and procedures, which we will discuss in this article.
What is Function?
A function, in the context of computer programming languages, is a set of instructions which takes some input and performs certain tasks. In SQL, a function returns a value. In other words, a function is a tool in SQL that is used to calculate anything to produce an output for the provided inputs. In SQL queries, when a function is called, it returns the resulting value and control to the calling function.
Functions in SQL have certain restrictions − they cannot use DML statements like INSERT, DELETE, UPDATE, etc. A function can be called through a procedure. Based on definition, there are two types of functions namely, predefined function and user−defined function. Functions always return a value, though it may be a NULL value.
What is a Procedure?
A procedure is a set of instructions which takes input and performs a certain task. In SQL, procedures do not return a value. In some programming languages like Java, procedures and functions are same and also called subroutines.
In SQL, a procedure is basically a precompiled statement which is stored inside the database. Therefore, a procedure is sometimes also called a stored procedure. A procedure always has a name, list of parameters, and compiled SQL statements. Unlike functions, procedures can execute DML operations and handle transactions.
Difference between Function and Procedure
Following are the important differences between SQL Function and SQL Procedure −
| Key | Function | Procedure |
|---|---|---|
| Definition | A function is used to calculate result using given inputs. | A procedure is used to perform certain task in order. |
| Call | A function can be called by a procedure. | A procedure cannot be called by a function. |
| DML | DML statements cannot be executed within a function. | DML statements can be executed within a procedure. |
| SQL Query | A function can be called within a query. | A procedure cannot be called within a query. |
| Compilation | A function is compiled each time it is called. | A procedure is compiled once and can be called multiple times without being compiled. |
| Return Value | A function returns a value and control to calling function or code. | A procedure returns the control but not any value to calling function or code. |
| Try−Catch | A function has no support for try−catch blocks. | A procedure has support for try−catch blocks. |
| SELECT Statement | A SELECT statement can have a function call. | A SELECT statement cannot have a procedure call. |
| Transaction Handling | A function cannot have explicit transaction handling. | A procedure can use explicit transaction handling. |
Conclusion
The most significant difference between functions and procedures is their purpose and behavior. Functions are designed to calculate and return a specific result using given inputs, making them ideal for computations that can be used within SQL queries. Procedures, on the other hand, are used to perform a series of tasks in order and can handle complex operations including DML statements and transaction management, but they do not return values directly.
72K+ Views
