Types of stored procedures:-

1.System Stored Procedures:System stored procedures are stored in the master database and these are starts with a sp_prefix. These procedures can be used to perform variety of tasks to support sql server functions for external application calls in the system tables

Ex: sp_helptext [StoredProcedure_Name]

2.User Defined Stored Procedures:User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_prefix because if we use the sp_prefix first it will check master database then it comes to user defined database.

3.Extended Stored Procedures:Extended stored procedures are the procedures that call functions from DLL files. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.