Please enable Javascript to correctly display the contents on Dot Net Tricks!

Different Types of SQL Server Stored Procedures

  Author : Shailendra Chauhan
Updated On : 01 Sep 2013
Total Views : 152,041   
Support : SQL Server 2005,2008,2012

A stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server. Benifit 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 reduce the network traffic. Hence stored procedure improve performance to execute sql statements. For more about stored procedure refer the article CRUD Operations using Stored Procedures.

Stored procedure can accepts input and output parameters. Stored procedure can returns multiple values using output parameters. Using stored procedure, we can Select,Insert,Update,Delete data in database.

Types of Stored Procedure

  1. System Defined Stored Procedure

    These stored procedure 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. These 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 Pocedure
    System Procedure
    It is used to rename an database object like stored procedure,views,table etc.
    It is used to change the owner of an database object.
    It provides details on any database object.
    It provide the details of the databases defined in the Sql Server.
    It provides the text of a stored procedure reside in Sql Server
    It provide the details of all database objects that depends on the specific database object.
  2. Extended Procedure

    Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in 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 any 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'

  3. User Defined Stored Procedure

    These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

  4. CLR Stored Procedure

    CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration of procedure was introduced with SQL Server 2008 and allow for procedure to be coded in one of .NET languages like C#, Visual Basic and F#. I will discuss CLR stored procedure later.


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

  2. The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.

  3. 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 with in a stored procedure


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

Free Interview Books
26 OCT
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM-09:00 AM IST

Know More
24 OCT
MEAN Stack Development (online)

MON-FRI 06:00 AM -07:30 AM IST

Know More
24 OCT
AngularJS Development (online)

MON-FRI 08:00 PM-09:30 PM IST.

Know More
23 OCT
PPC Marketing (offline)

Sat, Sun 09:00 AM-10:30 AM IST

Know More
22 OCT
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

20 OCT
NodeJS Development (online)

MON-FRI     06:00 AM - 07:30 AM IST

12 OCT
ASP.NET MVC with AngularJS Development (online)

Mon-Fri     09:30 PM-11:00 PM IST

17 SEP
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     05:00 PM-06:30 PM IST

NodeJS Development (offline)

Sat, Sun     11:00 AM-12:30 PM IST


Professional Speaks