Stored Procedure

If we want to do certain repetitive tasks/operations over our database within the same application and database, then in this case the most useful method for this functioning is none other than Stored Procedures.

It’s also known as "S-Proc" or "SP". 


Stored Procedure | Pros

A SQL Server Stored Procedure provides us many advantages like:

      - SP can return zero, single or multiple values
      - We can use transaction in SP
      - SP have both input and output parameters
      - Cab be used with XML for clause
      - Used in changing server configuration
      - SP allows DML statements as well as Selects

Stored Procedure | Hierarchy

The complete hierarchy of a Stored Procedure is shown below in a reference chart, from the root to the base level:

Stored Procedure | Types

A Stored Procedure is categorized in these two major categories:



System Stored Procedure

In SQL Server sometimes we need to do many informational or admin level tasks or activities. These sets of tasks can be done very easily using a System Stored Procedure. In this type of Stored Procedure whenever we do any modification or alteration in our database table, we do add a backup or some administrative level functioning in that modified database location.

All this functioning is done under the procedure of a system S-Proc. System Stored Procedures are prefixed by sp_, so it is not available to use sp_ for any other type of the Stored Procedure that we create until or unless they are part of our SQL Server installation.

User Stored Procedure

It is a program that is in general stored and compiled in our SQL Server and prefixed with sp_ like system Stored Procedure. 

This type of Stored Procedure can be further categorized into the three parts:
  • User Stored Procedure
  • User Defined Functions
  • Triggers
Stored Procedure | Creation

Here's a S-Proc example: