> For the complete documentation index, see [llms.txt](https://dailyjournal.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dailyjournal.gitbook.io/notes/databases/relational-databases/microsoft-sql-server/stored-procedures.md).

# 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

<table><thead><tr><th width="183">Stored Procedure</th><th>Description</th></tr></thead><tbody><tr><td>sp_who</td><td>provides information about the current user, session, and processes in the current instance.<br>syntax: <code>EXEC sp_who</code></td></tr></tbody></table>

## User-defined Stored Procedures

* `CREATE PROCEDURE` - used to create new stored procedures.

```sql
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.

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

* `DROP PROCEDURE` - used to remove existing stored procedures.

```sql
DROP PROCEDURE IF EXISTS [<stored_procedure_name>];  
GO
```

* `EXECUTE` - used to execute existing stored procedures.

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