Stored procedure

A 'stored procedure' (or SP) is the equivalent in a relational database of a Sub or Function in VB.NET.

It contains a sequence of SQL statements which are executed in order when the stored procedure is called.

As you'd expect, stored procedures can accept parameters and return values but don't have to.

Values can be returned : singly (usually integers), within output parameters, as result sets for each SELECT statement within the SP or as a global cursor.

Local variables can also be defined and used within an SP.

Confusingly, relational databases also support the concept of a 'user defined function' (or UDF) which has many similarities to a stored procedure.

As the name suggests, stored procedures are pre-compiled and then stored in the database.

In contrast, when a client appplication sends an inline query to the database, this has first to be compiled before the database can execute it. As they don't have the overhead of dynamic compilation, stored procedures are therefore more performant than inline queries.

However, most relational databases employ 'caching' to reduce this overhead i.e. queries are cached after they've been compiled and, if the same query is needed again, this is taken from the cache rather than compiled again. Also, stored procedures are unable to take into account information which may only be available at runtime to the query compiler.