SQL | Types of Stored Procedures
SQL Server categorizes the stored procedures mainly in two types:
User-defined Stored Procedures
System Stored Procedures
User-defined Stored Procedures
Database developers or database administrators build user-defined stored procedures.
A stored procedure specified by the user accepts input parameters and returns output parameters. DDL and DML commands are used together in a user-defined procedure.
User-defined Stored Procedures can further divide this procedure into two types:
1. T-SQL Stored Procedures:
Transact-SQL procedures are one of the most popular types of SQL Server procedures. It takes parameters and returns them.
These procedures handle INSERT, UPDATE, and DELETE statements with or without parameters and output row data.
2. CLR Stored Procedures:
The CLR indicates the common language runtime.
CLR stored procedure is a special type of procedure that is based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for the procedure to be coded in one of .NET languages like C#, Visual Basic and F#.
System Stored Procedures
The server's administrative tasks depend primarily on system stored procedures. The system stored procedures prevent the administrator from querying or modifying the system and database catalog tables directly.
These stored procedures are already defined in SQL Server. When SQL Server is installed, it creates system procedures. These are physically stored in hidden SQL Server Resource Database and logically appear in the sys schema of each user-defined and system-defined database. This procedure starts with the sp_ prefix.
Some Useful System Defined Stored Procedure
sp_rename- It is used to rename a database object like stored procedure, views, table etc.
sp_changeowner- It is used to change the owner of a database object.
sp_help- It provides details on any database object.
sp_helpdb- It provides the details of the databases defined in the SQL Server.
sp_helptext- It provides the text of a stored procedure reside in SQL Server
sp_depends- It provides the details of all database objects that depend on the specific database object.
Temporary Stored Procedure
We can create temporary procedures in the same way as we can create temporary tables.
The tempdb database is used to create such procedures.
These can be: -
A. Local Temporary Stored Procedures
We can create this type of procedure by using the # as prefix and accessed only in the session in which they were created.
A local temporary stored procedure is available only in the current session i.e. the window in which it was created, and is dropped when the session/that window closed is closed.
B. Global Temporary Stored Procedures
We can create this type of procedure by using the ## as a prefix and accessed from any sessions.
A global temporary stored procedure is visible to all sessions and is dropped when the session of the user that created it is closed.
- Keeping the window, in which both sprocs are created open, open a new query window and execute the procs again. You will notice that local temp stored proc will fail with ‘object not found' error, but global temporary stored proc will work,
- Now close the window in which you ran the create proc scripts and execute the procs in another window. You will notice that both will fail with object not found error since the session is now closed.
we can see our temporary stored procedures at
Extended Procedure
Extended procedures provide an interface to external programs for various maintenance activities.
These extended procedures start with the xp_ prefix and stored in the Master database. Basically, these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.
The below procedure will display details about the BUILTIN\Administrators Windows group.
