Different Types of stored procedure sql Server

Different Types of stored procedure sql Server

04 Dec 2024
Intermediate
335K Views
19 min read
Learn with an interactive course and practical hands-on labs

SQL Server Course

Stored Procedure in SQL

A stored procedure in SQL is a collection of pre-compiled SQL statements. It can be stored and run as a single unit. Instead of writing the same query over, you may encapsulate the logic in a stored procedure and call it as needed. Stored procedures can also accept parameters, making them useful for a variety of tasks such as data modification, retrieval, and even complicated business logic execution within a database.

In this SQL Server Tutorial, We are gonna explore more about Different Types of stored procedures in SQL Server including how they work.

Read More - Top DBMS Interview Questions and Answers

What is the 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.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execution

EXEC procedure_name;

SQL Stored Procedure Examples

1. Retrieve All Employees

Syntax:

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END; 

Execution:

EXEC GetAllEmployees;

2. Insert a New Employee

Syntax:

CREATE PROCEDURE AddEmployee 
    @Name NVARCHAR(100), 
    @Position NVARCHAR(50), 
    @Salary DECIMAL(10, 2)
AS
BEGIN
    INSERT INTO Employees (Name, Position, Salary)
    VALUES (@Name, @Position, @Salary);
END;

Execution:

EXEC AddEmployee 'John Doe', 'Software Engineer', 75000.00;

3. Update Employee Salary

Syntax:

CREATE PROCEDURE UpdateSalary 
    @EmployeeID INT, 
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE ID = @EmployeeID;
END;

Execution:

EXEC UpdateSalary 101, 80000.00;

4. Delete an Employee

Syntax:

CREATE PROCEDURE DeleteEmployee 
    @EmployeeID INT
AS
BEGIN
    DELETE FROM Employees
    WHERE ID = @EmployeeID;
END;

Execution:

EXEC DeleteEmployee 105;

Types of Stored Procedure in SQL Server

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 Stored Procedures

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

Database developers or administrators create user-defined stored procedures. 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

Creating Stored Procedures in 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.

1. Stored Procedure with One Parameter

A stored procedure with one parameter is used for operations that require a single input value.

Example:

CREATE PROCEDURE GetEmployeeByID (@EmployeeID INT)
AS
BEGIN
    SELECT * FROM Employees WHERE ID = @EmployeeID;
END; 

Execution:

EXEC GetEmployeeByID 101; 

2. Stored Procedure with Multiple Parameters

A stored procedure with multiple parameters allows for more complex operations that need multiple input values.

Example:

CREATE PROCEDURE UpdateEmployeeSalary (@EmployeeID INT, @NewSalary DECIMAL(10, 2))
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE ID = @EmployeeID;
END; 

Execution:

EXEC UpdateEmployeeSalary 101, 75000.50; 

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 do you 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 do you 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. Also, consider our SQL Server Course for a better understanding of SQL concepts

FAQs

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

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.

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.

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

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

Take our Sqlserver skill challenge to evaluate yourself!

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

GET FREE CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at ScholarHat)

Shailendra Chauhan, Founder and CEO of ScholarHat by DotNetTricks, is a renowned expert in System Design, Software Architecture, Azure Cloud, .NET, Angular, React, Node.js, Microservices, DevOps, and Cross-Platform Mobile App Development. His skill set extends into emerging fields like Data Science, Python, Azure AI/ML, and Generative AI, making him a well-rounded expert who bridges traditional development frameworks with cutting-edge advancements. Recognized as a Microsoft Most Valuable Professional (MVP) for an impressive 9 consecutive years (2016–2024), he has consistently demonstrated excellence in delivering impactful solutions and inspiring learners.

Shailendra’s unique, hands-on training programs and bestselling books have empowered thousands of professionals to excel in their careers and crack tough interviews. A visionary leader, he continues to revolutionize technology education with his innovative approach.
Accept cookies & close this