What is the difference between Function and Procedure?

Question

What are the differences between the two, and examples of where and generally are used.

Answer 1

Functions and procedures serve different purposes.

A function, thinking of its mathematical definition, is usually used to calculate a value based on a given input. A function does not allow changes outside its "Scope" (scope), that is, it cannot be used to change the overall state of the database (for example, through the INSERT, UPDATE, DELETE instructions).

Functions can be directly embedded in an SQL statement if they return a scalar value

SELECT udf_DiaSemana(data_hoje) 

Or they can be used at a junction if they return a table

SELECT t1.Var1, f1.Var2
FROM tbl_tabela1 t1
INNER JOIN udf_Exemplo(parametro) f1
   ON f1.Var1 = t1.Var1

On the other hand, the procedures can be seen as programs/scripts (if we make an analogy with any programming language). A precedent allows changing the overall state of the database (for example, using the INSERT, UPDATE, DELETE instructions). Procedures are typically used to merge multiple queries into a single transaction.

Small differences between the two concepts:

• We can execute a function from a precedent, but we cannot do the reverse.

• We can use functions in conjunction with the SELECT, WHERE, HAVING instructions but it is not possible to do the same with procedures.

• Procedures allow handling of exceptions via Try/catch. This is no longer possible in a function.

Answer 2

briefly: functions return values, procedures do not.