Stored Procedures

A stored procedure in SQL Server is a group of one or more Transact-SQL statements.

Types of Stored Procedures:

• System-defined stored procedures

• User-defined stored procedures

System-defined stored procedures

Stored Procedure	Description
sp_who	provides information about the current user, session, and processes in the current instance. syntax: EXEC sp_who

User-defined Stored Procedures

• CREATE PROCEDURE - used to create new stored procedures.

USE database_name;
GO
CREATE PROCEDURE stored_procedure_name
    @variable_name datatype (optional)
AS
    sql_statements
GO

• ALTER PROCEDURE - used to alter existing stored procedures.

ALTER PROCEDURE stored_procedure_name
    @variable_name datatype (optional)
AS  
    sql_statements
GO

• DROP PROCEDURE - used to remove existing stored procedures.

DROP PROCEDURE IF EXISTS [<stored_procedure_name>];  
GO

• EXECUTE - used to execute existing stored procedures.

EXECUTE procedure_name;
USE database_name;  
GO  
EXEC stored_procedure_name @variable_name = <value>;
GO