A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.

Types of Stored Procedure

Basically there are two types of stored procedure first is System Stored Procedure and second one is User Define Stored procedure.

System Stored Procedure:

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures.

System stored procedure is those stored procedure which is available by the SQL server such as for renaming table name there is one stored procedure namely SP_RENAME , so such types of stored procedure are called system stored procedure.

User Define Stored Procedure:

User Define stored procedure are those procedure which are developed by user for obtaining particular goal of task. The user are restrict to create stored procedure in master database with prefix name sp of stored procedure, because sp prefix name is used for system stored procedure in master database.

Creating a User Define Stored Procedure

You create stored procedures in the SQL Server Management Studio (SSMS) using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

Syntax for Stored Procedure

--SYNTAX DEMONSTRATION OF CREATING USER DEFINE STORED PROCEDURE 
CREATE PROCEDURE <PROCEDURE_NAME>
(
  -- PARAMETER OF STORED PROCEDURE 
)
AS
BEGIN
--PROCEDURE BODY 
--WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END
GO

SET ANSI_NULLS ON -- that compares a value with a null return a 0(zero)
GO
SET QUOTED_IDENTIFIER ON -- double quotation mark is used as part of the SQL Server identifier(object name)
GO
 
-- creating procedure
 
CREATE PROCEDURE TEST_PROCEDURE 
      -- Add the parameters for the stored procedure here
      @ID INT
AS
BEGIN
    -- SELECT statements for procedure here
      SELECT * FROM info where id = @ID
END

EXEC TEST_PROCEDURE '1' -- calling stored procedure with passing parameter

DROP PROC TEST_PROCEDURE -- we can use PROC in place of PROCEDURE keyword
Alter Stored Procedure
-- Modify the Procedure
 
ALTER PROC TEST_PROCEDURE
      -- Add the parameters for the stored procedure here
      @NAME VARCHAR(50)
AS
BEGIN
    -- SELECT statements for procedure here
      SELECT * FROM info where [name] = @NAME
END