Different Types of stored procedure sql Server

Print 4 min read

31 Aug 2022

Intermediate

321K Views

A stored procedure is a precompiled set of one or more SQL statements that are stored on SQL Server. The benefit of Stored Procedures is that they are executed on the server-side and perform a set of actions, before returning the results to the client-side. This allows a set of actions to be executed with minimum time and also reduces the network traffic. Hence stored procedure improves performance to execute SQL statements. For more about stored procedure refer to the article CRUD Operations using Stored Procedures.

A stored procedures can accept input and output parameters. Stored procedures can return multiple values using output parameters. Using stored procedure, we can Select, Insert, Update, Delete data in the database.

Types of Stored Procedure

System Defined Stored Procedure
These stored procedures are already defined in SQL Server. 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. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.

System Defined Stored Procedure

System Procedure

Description

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.
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.

Example Below statements are used to log an event in the NT event log of the server without raising an error on the client application.
```
 declare @logmsg varchar(100)
set @logmsg = suser_sname() + ': Tried to access the dotnet system.'
exec xp_logevent 50005, @logmsg
print @logmsg 
```
Example The below procedure will display details about the BUILTIN\Administrators Windows group.
```
 EXEC xp_logininfo 'BUILTIN\Administrators'
```
User-Defined Stored Procedure
These procedures are created by the user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.
CLR Stored Procedure
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#. I will discuss the CLR stored procedure later.

Note

We can nest stored procedures and managed code references in SQL Server up to 32 levels only. This is also applicable for function, trigger, and view.
The current nesting level of a stored procedure's execution is stored in the @@NESTLEVEL function.
In SQL Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked within a stored procedure

Summary

In this article, I try to explain the types of Stored Procedure in SQL Server. I hope after reading this article you will be aware of the stored procedure. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.