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_