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.

SQL Server Stored procedure -

Define stored procedure.

Answer
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)

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.