SQL Server stored procedure

Define Stored Procedures.

A stored procedure is a compiled set of Transact-SQL statements.
The business logic can be encapsulated using stored procedure.
It improves network traffic by running set of Transact-SQL statements at one go.

Define extended Stored Procedures.

An extended stored procedure compiles as DLL and are created to expand capabilties of user defined stored procedure. It uses xp_ prefix as naming convention.

What are the purposes and advantages stored procedure?

Answer
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.

Answer

• 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.