Background

Many times in interview one question commonly used that is what is stored procedure and its types, so by considering above requirement i have decided to write this blog to help freshers and job seekers also who might be wants to know about it.
So let us see

Types Of Stored Procedures

  • User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database

  • System Stored Procedure: The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database

  • Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server

  • Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission

  • Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_