Functions and procedures are probably some of the most common words in programming terminology you will hear, and occasionally people even use these terms interchangeably, which is, you guessed it, wrong. Therefore, the question we will explore in this article is, what is a function, a procedure, the difference in programming language terminology between function and procedure, and most of all what is the difference in terms of function vs. procedure functionality and their use. So, let’s dig it.
What is a function?
The name function comes from math, and we’re sure you’re aware that in math a function is used to calculate a value-based input that is stored in that function. A bit more rigid definition of a function says a function is applied when you want to calculate a result by using specific inputs.
What is a procedure?
A definition of a procedure says a procedure is a set of commands – a sequence of instructions – which can be executed in order. We use a procedure when we want to perform a specific task.
Function vs. procedure
A function returns a value. A procedure executes instructions. However, in most programming languages, even functions can have a set of commands. Hence the basic difference between a function and a procedure is only in function returning a value.
SQL is specific regarding functions vs. procedures
In terms of SQL, you can call a function within a query, but not a procedure. When you call a function, the function is first compiled, and then it is being called. However, a procedure is compiled once and then the procedure can be called multiple times without being compiled. When you call a function, a function returns a value and control. However, when you call a procedure, it will return the control but not any value.
A function cannot call a procedure, but a procedure can call a function.
Functions can be used in SQL statements anywhere in a Where (or Having or Select) block, while procedures can’t.
In SQL, you can treat functions that return tables the same as another rowset, and this is neat because it can be used in a Join block with other tables. In addition, inline functions can be seen as views that take parameters. Therefore, they can be used in Join blocks and other rowset operations.
You cannot perform DML(Data Manipulation Language) statements within a function. The function allows only Select statement in it. But you can carry out DML statements within a procedure. In addition, a procedure allows Select as well as DML (Insert, Update, Delete) statements in it.
A Select statement can have a function call – functions can be embedded in a select statement, but procedures can not be used in a select statement.
A function is not supported for Try-catch blocks, while exceptions can be handled by Try-catch blocks in a procedure.
A function can not use explicit transaction handling, while a procedure can use it.
However, at the end of the day, it is essential to bring out that a difference between a function and a procedure largely depends on the context – a programming language.
For example, in languages that resemble Pascal, such as Object Pascal, Oberon, Ada, and Lua, functions and procedures are two individual units, and as said above they contrast in returning a value – function does return a value, a procedure doesn’t. In terms of the language syntax, functions and procedures are different too – for example, you will not form a statement with a function call, but with a procedure call you will form it; you have to use function calls in other statements, but you cannot use a procedure call inside an expression.
In programming languages that favor C, the difference between procedures and functions is not so evident, so this is probably the reason some developers use these two terms as synonyms.
On the other hand, in functional languages, such as Scala, Scheme, Clojure and others there is no such thing as a procedure – everything is a function.