Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 relation 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. It also controls to the calling function. However, in a function, we cannot use some DML statements like Insert, Delete, Update, etc.
Also, a function can be called through a procedure. Based on definition, there are two types of functions namely, predefined function and userdefined function. Another important point about functions is that they may or may not return a value, i.e. a function can return a null valued as well.
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 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. In SQL, a procedure does not return any value.
Now, let us discuss the differences between function and procedure in detail.
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. |
SQL, Call |
Whenever a function is called, it is first compiled before being called. |
A procedure is compiled once and can be called multiple times without being compiled. |
SQL, Return |
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 |
A procedure has support for try-catch blocks. |
SELECT |
A select statement can have a function call. |
A select statement can't have a procedure call. |
Explicit Transaction Handling |
A function cannot have explicit transaction handling. |
A procedure can use explicit transaction handling. |
Conclusion
The most significant difference that you should note here is that a function is used to calculate the result using the given inputs, while a procedure is used to perform a certain task in order.
57K+ Views
