Stored procedure

Question

Hi What is a stored procedure? Why it is better than inline query?

Rohatash Kumar · Answer

Eddie see our recommended articles section on the right side.

Vulpes · Answer

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.