A Stored Procedure is a procedure of a database able to carry out elaborate procedures inside the same database. It is a program written in SQL or other languages arising from SQL, such as ASP, ASP.NET, Visual Basic or other.
According to the features, it can be divided into:
- Functions (accept inout/out parameters and return only a value);
- Procedure (accepts in-out/out parameters, but they do not return any value);
- Trigger (which are caused by the owners).
The main advantages which come from the use of the Stored procedures are the following:
Prevent the client from rewriting elaborate query. The procedure is filled in and stored inside the same database, ensuring an improvement in the performances and preventing from exchanging an excessive number of information between Client and Server.
As they are written in proprietary versions of SQL and as they are therefore real structural languages you can carry out elaborate procedures which would be not realizable using only SQL query.
Maintaining some functions libraries, usually used inside the same database.
Carrying out the operations reminding to a different procedure without knowing the structure of a database or having a restricted knowledge ( this allows the database administrator the advantage of according only the execution permission of the stored procedure, avoiding to assign the permissions of modification and/or reading).
Moreover, it involves a substantial saving of the resources of the net, saving directly proportional to the amount of code contained in the stored procedure and to the frequency of invocation of the same.