Stored Procedures are
pre-compile objects which are compiled for first time and its compiled format
is saved which executes (compiled code) whenever it is called.
Purposes
and advantages of stored procedures:
- Manage, control and validate
data
- It can also be used for access
mechanisms
- Large queries can be avoided
- Reduces network traffic since
they need not be recompiled
- Even though the stored
procedure itself may be a complex piece of code, we need not write it over
and over again. Hence stored procedures increases reusability of code
- Permissions can be granted for
stored procedures. Hence, increases security.
Determine when to use stored procedure to
complete SQL Server tasks.
- If a large piece of code needs
to be performed repeatedly, stored procedures are ideal
- When hundreds of lines of SQL
code need to be sent; it is better to use stored procedure through a
single statement that executes the code in a procedure, rather than by
sending hundreds of lines of code over the network.
- When security is required.