Stored Procedure And Its Types - SQL

A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

    * They allow modular programming.
    * They allow faster execution.
    * They can reduce network traffic.
    * They can be used as a security mechanism.

SP are classified as Following Types:

    * Temporary Stored Procedures
    * System stored procedures 
    * Automatically Executing Stored Procedures
    * User stored procedure

Temporary Stored Procedures:
SQL Server supports two types of temporary procedures:local and global.

A local temporary procedure is visible only to the connection that created it.

A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.

System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)

Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.

User stored procedure:

It is a Normal Procedure which are all written by user. It will be available in the User Database.