Browse Tutorials
 Different Types of stored procedure sql Server

Different Types of stored procedure sql Server

18 Mar 2024
Intermediate
325K Views
12 min read

Stored Procedure in SQL: An Overview

A stored procedure, as explained in this SQL Server Tutorial, is a precompiled set of one or more SQL statements that are stored on an 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. In this SQL Server Course, we will learn that stored procedures allow a set of actions to be executed with minimum time, thus significantly reducing network traffic. Hence stored procedure improves performance to execute SQL statements.

Stored Procedure

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

Types of Stored Procedure

1. System Defined Stored Procedure

Administrative tasks on the server are mostly dependent on system-stored routines. SQL Server creates system procedures when it is installed. The system-stored procedures prevent administrators from directly querying or altering system and database catalog tables. Developers frequently disregard system-stored processes.

System Defined Stored Procedure
System Procedure
Description
sp_rename
It renames 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 residing in the SQL Server
sp_depends
It provides the details of all database objects that depend on the specific database object.

2. Extended Procedure

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

This SQL code snippet generates a log message describing a user's attempt to access the "dotnet system," logs it to event 50005, and then displays it on the screen.

Example The below procedure will display details about the BUILTIN\Administrators Windows group.

 EXEC xp_logininfo 'BUILTIN\Administrators'

3. User-Defined Stored Procedure

User-defined stored procedures are created by database developers or database administrators. These procedures provide one or more SQL statements that can be used to select, update, or delete data from database tables. A user-specified stored procedure accepts input parameters and returns output parameters. In a user-defined procedure, DDL and DML commands in SQL are used simultaneously.

4. CLR Stored Procedure

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

Note

  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 procedure's execution is stored in the @@NESTLEVEL function.
  3. In SQL Server, the 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

Create Stored Procedure SQL Server

To create a stored procedure, use the SQL Server Management Studio (SSMS) user interface or Transact-SQL in an SSMS query window. Always make sure to use the most recent version of SSMS.

Create a simple stored procedure to insert data into a table

CREATE PROCEDURE InsertIntoTable ( 
 @param1 datatype1, 
 @param2 datatype2,
 ... )  -- Add more parameters if needed
AS
BEGIN
 INSERT INTO MyTable (column1, column2, ...)
 VALUES (@param1, @param2, ...);
END;
This procedure accepts parameters (@param1, @param2) and inserts them into appropriate columns (column1, column2) of the table MyTable. You can add additional parameters and columns as needed.

Create a stored procedure with parameters to filter data retrieval

CREATE PROCEDURE GetFilteredData (
 @filterValue varchar(50)  -- Adjust data type and size as needed
)
AS
BEGIN
 SELECT * 
 FROM MyTable
 WHERE ColumnToFilter LIKE '%'+ @filterValue + '%';
END;
This procedure receives a parameter @filterValue (modify the data type and size if necessary) and obtains all rows from MyTable where the ColumnToFilter contains the specified value (using LIKE with wildcards). You can adjust the filter condition (WHERE clause) to meet your requirements.

Create a stored procedure with output parameters

CREATE PROCEDURE CalculateDiscount (
 @originalPrice DECIMAL(10,2),  -- Input parameter for original price
 @discountRate INT,             -- Input parameter for discount rate
 @discountedPrice OUT DECIMAL(10,2) -- Output parameter for discounted price
)
AS
BEGIN
 DECLARE @discountAmount DECIMAL(10,2);  -- Local variable to hold calculation


 SET @discountAmount = @originalPrice * (@discountRate / 100.0);
 SET @discountedPrice = @originalPrice - @discountAmount;
END;
This method accepts two parameters: @originalPrice and @discountRate. It calculates the discount amount with a local variable and then assigns the final discounted price to the output parameter @discountedPrice. Remember that the calling program must additionally specify the output parameter using the OUT keyword for the value to be returned.

How to Create a Temporary Stored Procedure in SQL?

You can write procedures that are exclusive to your current connection or session by using temporary stored procedures. They are useful for one-time tasks or calculations during a certain session. There are two major types of temporary stored procedures in SQL Server:
  1. Local Temporary Stored Procedures
  2. Global Temporary Stored Procedures

1. Local Temporary Stored Procedures

These are specific to your connection and begin with a single hash symbol (#). They are instantly disconnected when you close your connection.

Example

CREATE PROCEDURE #GetRecentOrders ( @days INT )
AS
BEGIN
 SELECT * FROM Orders WHERE OrderDate >= DATEADD(day, -@days, GETDATE());
END;
This procedure, #GetRecentOrders, accepts a parameter @days and returns orders placed in the last @days. It will only be available during your current session.

2. Global Temporary Stored Procedures

These are preceded with a double hash symbol (##) and are accessible to all connections in the same SQL Server instance until the connection is closed.

Example

CREATE PROCEDURE ##CalculateTotalSales ( @productCategory VARCHAR(50), @outputTotal DECIMAL(10,2) OUT )
AS
BEGIN
 DECLARE @categorySales DECIMAL(10,2);


 SELECT @categorySales = SUM(Amount)
 FROM Sales S
 INNER JOIN Products P ON S.ProductID = P.ProductID
 WHERE P.Category = @productCategory;


 SET @outputTotal = @categorySales;
END;
This procedure, ##CalculateTotalSales, computes the total sales for a given product category and assigns the result to the output parameter @outputTotal.

How to rename stored procedures in SQL Server?

SQL Server does not allow you to change the name of a stored procedure. Because renaming a stored procedure does not change the name of the associated object in sys.sql_modules. As a result, if we need to update an existing stored procedure, we simply DROP it and recreate it under a new name.

Benefits of Stored Procedure

Stored processes have certain major benefits, which include:

  • Reusable: Numerous users and programs can easily access and reuse saved processes by simply calling them.
  • Easy to alter: Using the ALTER TABLE command, you can rapidly adjust the statements in a stored procedure as needed.
  • Secure: Stored procedures allow you to improve the security of an application or database by preventing users from directly accessing the table.
  • Low network traffic: The server merely sends the procedure name rather than the entire query, which reduces network traffic.
  • Improves performance: Upon initial use, a plan for the stored procedure is generated and saved in the buffer pool for speedy execution the next time.

Read More:

Summary

Pre-compiled SQL statements are provided by stored procedures for efficient database operations. They are classified into four types: system-defined for administrative duties, extended procedures with external access, user-defined for data manipulation, & CLR-based for custom logic. While nesting depth is limited, stored procedures improve efficiency & simplify database interaction overall.

FAQs

Q1. What are the 2 types of storage procedures?

There are two types of temporary procedures: local temporary stored procedures and global temporary procedures. The tempdb database is used to store these procedures.

Q2. What is a special type of stored procedure?

Stored Procedure in CLR is a form of stored procedure that makes use of Microsoft's Common Language Runtime (CLR) and can be written in languages such as C#, Visual Basic, or F#. Since 2008, it has used the Visual Studio integrated development environment for execution in an MSQL server database management system.

Q3. What are SQL Server stored procedures?

A stored procedure is a collection of SQL statements written and stored in a database management system that allows various users and programs to share and reuse the procedure. A stored procedure can accept input parameters, conduct the specified operations, and return a variety of output values.

Q4. How many various types of stored procedures are there?

Three types of procedures are supported by the Stored Procedures stage: Procedures must be altered. Procedures for gathering information. Procedures to be followed.

Q5. What are the three types of data storage?

The three most common types of data storage are file storage, block storage, and object storage, with each serving a specific purpose.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this