What are the different types of procedures?

Procedures are segregated into 3 parts:

1. User defined Stored procedure

These are procedures which are created via user on the user databases, users create these procedures to get accomplish specific tasks. It can be created via Database administrator or database developer.

2. System Stored procedure: it is the system defined procedures which has prefix as sp_. These are mainly used for administrative or information purposes.

3. Extended Stored procedure: These are routine procedures which are present in DLLs that work similarly to regular procedures. They communicate (send/ receive data) via SQL Server's Open Data Services API and written in C or C++. They are stored in a master database and execute within the SQL Server process space.