Stored Procedure in SQL SERVER

What is Stored Procedure?

  In SQL Server, A stored procedure is a collection of group of one or more Transact-SQL statements. And execute in a single unit and save under a single name.

Types of Stored Procedures

•      System Defined Store Procedure

•      User-Defined Store Procedure

System Defined Store Procedure:

•      System Defined Store Procedure are already defined in Sql Server

•      It useful in performing administrative and informational activities in SQL Server.

Example:

•      Sp_help: The Sp_help is a system Stored Procedure that Reports information about a database object, a user-defined data type, or a data type. It will only return information for objects that are able to be referenced within the database you are currently in.

Sp_table:   The Sp_table is a system Stored Procedure that reports information about the list of the tables from the database.

•      User-Defined Store Procedure

A user-defined procedure can be created in a user-defined database But not in the master database.

Example:

Create procedure SP_Emp

@ Emp_Name Varchar (50),

@ Emp_contactno int 

As

Begin

Select   Emp_Name, Emp_contactno From EMP

End

Go

For More : Syntax for update stored procedure in SQLServer , Stored Procedure in SQL SERVER , Difference between Function and Stored Procedure