What is the difference between Function and Procedure?

Functions and procedures serve different purposes.

A function, considering its mathematical definition, is normally used to calculate a value based on a given input. A function does not allow changes outside its scope, that is, it can not be used to change the global state of the database (for example, through the INSERT, UPDATE, DELETE statements).

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

SELECT udf_DiaSemana(data_hoje) 

Or they can be used in a join 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 procedure allows you to change the overall state of the database (for example, using the INSERT, UPDATE, DELETE statements). Procedures are commonly used to join multiple queries into a single transaction.

Small differences between the two concepts:

• We can execute a function from a procedure, but we can not do the inverse.

• We can use functions in conjunction with the SELECT, WHERE, HAVING statements but can not do the same with procedures.

• Procedures allow you to perform exception handling via try / catch. The same is not possible in a function.

briefly: functions return values, not procedures.