What are the differences between the two, and examples of where and generally are used.
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" (scope), that is, it cannot be used to change the global state of the database (for example, through INSERT, UPDATE, DELETE instructions).
Functions can be embedded directly in an SQL statement if they return a scalar value.
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
Procedures, on the other hand, can be seen as programs/scripts (if we make an analogy with any programming language). A procedure allows changing the global state of the database (for example, using the INSERT, UPDATE, DELETE instructions). Procedures are commonly used to merge multiple queries into a single transaction.
Small differences between the two concepts:
We can execute a function from a procedure, but we cannot do the reverse.
We can use functions in conjunction with SELECT, WHERE, HAVING statements, but we cannot do the same with procedures.
Procedures allow handling exceptions, via try/catch. The same is not possible in a function.