25
AprDifferent Types of stored procedure sql Server
SQL Server Course
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.
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
- 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
Create Stored Procedure 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;
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 to 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 to 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:
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?
Q2. What is a special type of stored procedure?
Q3. What are SQL Server stored procedures?
Q4. How many various types of stored procedures are there?
Q5. What are the three types of data storage?
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.