24
JanDifferent 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
- 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, 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
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;
How do you create a temporary stored procedure in SQL?
- Local Temporary Stored Procedures
- 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
FAQs
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.