Different Types of stored procedure sql Server

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.

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

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;

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;

1. Stored Procedure with One Parameter

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

EXEC GetEmployeeByID 101; 

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

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;

How do you create a temporary stored procedure in SQL?

1. Local Temporary Stored Procedures
2. Global Temporary Stored Procedures

1. Local Temporary Stored Procedures

Example

CREATE PROCEDURE #GetRecentOrders ( @days INT )
AS
BEGIN
 SELECT * FROM Orders WHERE OrderDate >= DATEADD(day, -@days, GETDATE());
END;

2. Global Temporary Stored Procedures

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;

How do you rename stored procedures in SQL Server?

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:

• Top SQL Server Interview Questions and Answers
• Stored procedures (Database Engine) - SQL Server
• Create a stored procedure - SQL Server

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

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