Define Stored Procedure, advantages and when to use ?

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.