SQL Stored Procedures

SQL Stored Procedures are key to writing efficient, secure, and reusable logic for the database. They simplify complex operations and reduce repeated code in applications. Stored Procedures provide performance improvement by executing the precompiled query and improve the maintainability of your application. By minimizing the number of SQL statements, they reduce network traffic to the server. In this blog, let’s explore the concepts of SQL Stored Procedures in detail.

Table of Contents:

What are Sto red Procedures in SQL?
Key Features of SQL Stored Procedures
When do we need to use Stored Procedures in SQL?
How do you create a stored procedure in an SQL server?
Types of SQL Stored Procedures
Parameterized Stored Procedures in SQL Server
Types of Parameters in SQL Stored Procedures
Real-world Examples
Benefits of Stored Procedures in SQL
Common Mistakes and Best Practices in SQL Stored Procedures
Conclusion

What are Stored Procedures in SQL?

SQL Stored Procedures are collections of SQL statements that are stored and run in the database. Stored Procedures are precompiled, so they run faster than individual queries issued from applications. Stored Procedures can accept input parameters, return output parameters, and a result set. They allow business logic to be encapsulated, which makes the code more modular and reusable. They allow centralization of logic and, thus, provide security, maintainability, and consistency between applications.

For a better understanding of stored procedures in SQL, let’s create a Staff Directory table that can be used as an example for the following methods.

CREATE TABLE StaffDir (
    S_code INT PRIMARY KEY,
    S_name NVARCHAR(100),
    U_code INT,
    Role NVARCHAR(100),
);

INSERT INTO StaffDir (S_code, S_name, U_code, Role) VALUES
(1, 'Aarav', 101, 'Project Lead'),
(2, 'Saanvi', 102, 'Business Analyst'),
(3, 'Rohan', 103, 'Database Administrator'),
(4, 'Anaya', 101, 'Quality Engineer'),
(5, 'Vivaan', 102, 'DevOps Engineer'),
(6, 'Diya', 104, 'UX Designer'),
(7, 'Krishl', 103, 'System Analyst'),
(8, 'Meera', 104, 'Data Engineer'),
(9, 'Arjun', 101, 'Full Stack Developer'),
(10, 'Ishita', 102, 'Support Specialist');

SELECT
    S_code,
    LEFT(S_name, 10) AS S_name,
    U_code,
    LEFT(Role, 20) AS Role
FROM StaffDir;

This is how the Staff Directory table looks.

Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Key Features of SQL Stored Procedures

Modularity: Stored Procedures let you organize related SQL logic into a single reusable package.
Reusability: Stored Procedures can be executed multiple times without rewriting the SQL code.
Performance: Stored Procedures run faster because they are precompiled and optimized in the database engine.
Security: Access to data can be secured by allowing a user permission to execute a Stored Procedure, instead of accessing a table directly.
Maintainability: It is easier to maintain the logic of a Stored Procedure when that logic has been packaged in a Stored Procedure; it gets applied whenever the Stored Procedure is executed.

Syntax:

CREATE PROCEDURE pr_name
    @parameter1 datatype = default_value,  
AS 
BEGIN 
   -- User Logic 
END;

Advantages of SQL Stored Procedures

Better Performance: Runs more quickly as it is precompiled.
Code Reuse: Code can be reused multiple times.
Less Network Traffic: Logic runs on the server as opposed to the client.
High Security: Users can be given access to procedures without giving access to the table.
Reduced Maintenance: Changes are made in one place and do not affect any calling applications.

When do we need to use Stored Procedures in SQL?

Executing repetitive tasks: In case there is a need to repeat the same SQL operation several times, like a report or batch update.
Handling complex business logic: If business logic involves multiple steps, conditions, or calculations, the logic can be grouped.
Increasing performance: In case performance matters, stored procedures run faster due to precompilation.
Increasing security: In case you want to limit direct access to tables and provide access through the stored procedures.
Reducing network traffic: In case you want to reduce the amount of traffic between the application and the database server.
Centralizing logic: If you want to keep the logic together to make updates easier and for consistency.

How do you create a stored procedure in an SQL server?

To create a stored procedure in SQL Server, we can define a single block of SQL code that is given a name and executed later using an optional parameter.

Syntax:

CREATE PROCEDURE pr_name
    @parameter1 datatype = default_value,  
AS 
BEGIN 
   -- User Logic 
END;

Example:

GO
-- To create a stored procedure
CREATE PROCEDURE GetStaffByUnit 
    @UnitCode INT 
AS 
BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE U_code = @UnitCode; 
END;
GO

-- Execute the procedure
EXEC GetStaffByUnit @UnitCode = 102;

Parameters:

CREATE PROCEDURE is used to define a new stored procedure.
@UnitCode is a parameter provided as input when calling the stored procedure.
EXEC will run the stored procedure.
The GO statement signals that the procedure has been successfully created.

Output:

How do you create a stored procedure in an SQL server?

Explanation: Here, the query creates a stored procedure named GetStaffByUnit that retrieves the staff details where UnitCode = 102.

Types of SQL Stored Procedures

Based on the purpose or system integration, Stored Procedures in SQL Server can be categorized into many types.

1. User-Defined Stored Procedures

To perform specific tasks, these are the custom procedures created by developers.

Features of User-Defined Stored Procedures

It can accept input, output, and return values.
Complex business logic can be included.
It supports error handling and transactions.

2. System Stored Procedures

These system stored procedures are built-in procedures that are stored in the master database. These procedures generally begin with sp_

Features of System Stored Procedures

This is mainly used for tasks like backup and viewing metadata.
Since they are pre-existing, they do not require manual creation.
It is provided by SQL Server for manual operations.

3. Temporary Stored Procedures

These Stored Procedures exist temporarily, and when the session ends, it is dropped automatically.

Features of Temporary Stored Procedures:

It is created for short-term use during a session.
When the session or server ends, it is automatically deleted.
It is useful for testing.

4. Parameterized Stored Procedures

User-defined procedures that can accept input and/or output parameters, allowing them to be more dynamic.

Features of Parameterized Stored Procedures:

By avoiding repeated SQL compilation, performance is improved.
It is often used in reports and filtering.
It is used to reduce code duplication and supports the reusability of code.

Get 100% Hike!

Master Most in Demand Skills Now!

Parameterized Stored Procedures in SQL Server

Parameterized Stored Procedures accept input values at runtime, which allows dynamic and reusable execution logic based on the user-provided data.

Stored Procedure with one parameter

To filter or process data dynamically, a stored procedure with one parameter accepts a single input value. To execute logic based on a specific value, this can be useful.

Syntax:

CREATE PROCEDURE P_Name
    @P_Name Dt
AS
BEGIN
    -- logic
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffByUnit 
    @UnitCode INT 
AS 

BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE U_code = @UnitCode; 
END;');
EXEC GetStaffByUnit @UnitCode = 101;

Output:

Explanation: Here, the query creates a procedure called StaffDir based on a given Unit Code, and the result filters rows where U_code = 101.

Stored Procedure with Multiple Parameters

A Stored Procedure with multiple parameters can filter or take action based on one control input parameter or two.

Syntax:

CREATE PROCEDURE P_Name
    @P_Name Dt
AS
BEGIN
    -- logic
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffByUnitAndRole 
    @UnitCode INT, 
    @Role NVARCHAR(100) 
AS 
BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE U_code = @UnitCode AND Role = @Role; 
END;');
EXEC GetStaffByUnitAndRole @UnitCode = 102, @Role = 'Business Analyst';

Output:

Stored Procedure with Multiple Parameters

Explanation: Here, this query uses both U_code and Role as input, and it returns rows that satisfy the condition. Thus, it shows the staff name with Unit code = 102 and Role = ‘Business Analyst’.

Stored Procedure with Default Parameters

If the user doesn’t pass any input value, a stored procedure with default parameters assigns a value.

Syntax:

CREATE PROCEDURE proname
    @par1 dt = val 
AS 
BEGIN 
    -- logic 
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffByDefaultUnit 
    @UnitCode INT = 101 
AS
BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE U_code = @UnitCode; 
END;');
EXEC GetStaffByDefaultUnit;

Output:

Stored Procedure with Default Parameters

Explanation: Here, it returns staff with U_code = 101 from the StaffDir table. Since no parameters are passed, the @UnitCode defaults to 101.

Stored Procedure with NULL/Optional Parameters

This enables us to make parameters optional and also make dynamic decisions on conditions when they are NULL. This is useful for flexible filters when the user may not have to enter inputs.

Syntax:

CREATE PROCEDURE proname 
    @par1 dtype = NULL 
AS 
BEGIN 
    -- Logic
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffFlexible 
    @UnitCode INT = NULL 
AS
BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE (@UnitCode IS NULL OR U_code = @UnitCode); 
END;');
EXEC GetStaffFlexible;

Output:

Stored Procedure with NULL/Optional Parameters

Explanation: Here, the condition evaluates to true for all rows because @UnitCode is NULL, as the procedure returns a full StaffDir list.

Stored Procedure with Output Parameters

These procedures return a value to the user through OUTPUT parameters. This can be used to get single values like counts and totals from the procedure.

Syntax:

CREATE PROCEDURE proc_name 
    @param1 dtype, 
    @op_para datatype OUTPUT 
AS 
BEGIN 
    -- logic
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffCountByUnit 
    @UnitCode INT, 
    @StaffCount INT OUTPUT 
AS 
BEGIN 
    SELECT @StaffCount = COUNT(*) FROM StaffDir WHERE U_code = @UnitCode; 
END;');
DECLARE @Total INT;
EXEC GetStaffCountByUnit @UnitCode = 102, @StaffCount = @Total OUTPUT;
SELECT @Total AS StaffCount;

Output:

Explanation: Here, this procedure counts the rows where U_code = 102 and assigns the count value to @Total. The final SELECT will output the number from the output parameter.

Types of Parameters in SQL Stored Procedures

The different types of parameters in SQL Stored Procedures include IN, OUT, and INOUT.

1. IN Parameters in SQL Stored Procedure

These are default parameters in SQL that are used to pass values into a stored procedure.

Syntax:

CREATE PROCEDURE proc_name 
    @param1 dtype 
AS 
BEGIN 
    -- para 
END;

Example:

EXEC ('CREATE PROCEDURE GetStaffByUnit_IN 
    @UnitCode INT 
AS 
BEGIN 
    SELECT
        S_code,
        LEFT(S_name, 10) AS S_name,
        U_code,
        LEFT(Role, 20) AS Role 
    FROM StaffDir 
    WHERE U_code = @UnitCode; 
END;');
EXEC GetStaffByUnit_IN @UnitCode = 104;

Output:

Explanation: Here, the value 104 is passed into the @Unitcode parameter. Thus, it filters the rows where U_code = 104.

2. OUT Parameters in SQL Stored Procedure

These parameters are used to return data to the user from the stored procedure.

Syntax:

CREATE PROCEDURE proc_name 
    @op_para datatype OUTPUT 
AS 
BEGIN 
    -- op_para 
END;

Example:

EXEC ('CREATE PROCEDURE GetTotalStaff_OUT 
    @Total INT OUTPUT 
AS 
BEGIN 
    SELECT @Total = COUNT(*) FROM StaffDir; 
END;');
DECLARE @Result INT;
EXEC GetTotalStaff_OUT @Total = @Result OUTPUT;
SELECT @Result AS TotalStaff;

Output:

Explanation: Here, the procedure assigns the count of all values to @Total, and then the user extracts this value through the OUTPUT parameter.

3. INOUT Parameters in SQL Stored Procedure

By using both input and output, we can simulate the INOUT parameter since SQL Server does not support it directly. It will behave as an IN parameter initially, but it will modify its value after the first reference before returning.

Syntax:

CREATE PROCEDURE proc_name 
    @para1 dtype OUTPUT 
AS 
BEGIN 
     -- Logic
END;

Example:

EXEC ('CREATE PROCEDURE DoubleInput_INOUT 
    @Value INT OUTPUT 
AS 
BEGIN 
    SET @Value = @Value * 2; 
END;');
DECLARE @Num INT = 5;
EXEC DoubleInput_INOUT @Value = @Num OUTPUT;
SELECT @Num AS DoubledValue;

Output:

Explanation: Here, the INOUT accepts the input 5, and it is doubled inside the procedure and returned as 10.

Real-world Example

1. Library Management System

A college wants to know how many books are available in a specific department.

Example:

CREATE TABLE LibraryStock (
    Book_ID INT PRIMARY KEY,
    Book_Title NVARCHAR(100),
    Subject_Code INT,
    Author_Name NVARCHAR(100)
);

INSERT INTO LibraryStock (Book_ID, Book_Title, Subject_Code, Author_Name) VALUES
(101, 'Advanced Physics', 301, 'Dr. Sudhir'),
(102, 'Organic Chemistry', 302, 'Dr. Meena'),
(103, 'Modern Biology', 301, 'Dr. Kavita'),
(104, 'Indian History', 303, 'Dr. Arvind'),
(105, 'Thermodynamics', 301, 'Dr. Reema');

EXEC ('CREATE PROCEDURE GetBookCountBySubject 
    @SubjectCode INT, 
    @TotalBooks INT OUTPUT 
AS      
BEGIN 
    SELECT @TotalBooks = COUNT(*) FROM LibraryStock WHERE Subject_Code = @SubjectCode; 
END;');
DECLARE @BookResult INT;
EXEC GetBookCountBySubject @SubjectCode = 301, @TotalBooks = @BookResult OUTPUT;
SELECT @BookResult AS BooksAvailable;

Output:

Explanation: Here, the procedure returns the number of books in a department after receiving a subject code (301) as input. The OUTPUT parameter is used to store result 3 in @Result.

Benefits of Stored Procedures in SQL

Code Reusability: This stored procedure code can be reused for unlimited number of times in several applications.
Centralized Logic: Business logic, written within procedures, exists in the same place, which makes maintaining and updating it simple.
Enhanced Security: You can assign permissions to stored procedures instead of tables, which helps to protect any underlying data.
Minimized Network Activity: You now only have to make a single call to execute a procedure, whereas previously, you could have made several SQL calls sent from the client.
Easier Debugging/Testing: Stored procedures can be tested, logged, and debugged step by step as an independent process.

Common Mistakes and Best Practices in SQL Stored Procedures

Common Mistakes in Stored Procedures

Inappropriate Parameter Use: SQL injection exploits may result from improper parameter use.
Using SELECT *: Using SELECT * can cause a performance decrease and create issues if the table structure is changed.
Not using any error handling: By not using any TRY…CATCH blocks will make the debugging and recovery process difficult.

Best Practices for Stored Procedures

Utilize Parameters Properly: Parameters should always be used to pass inputs to prevent injection and aid reusability.
Use transactions if it is appropriate to do so: If atomicity is important, use transactions around the related data operations.
Test the parameter input: Test the stored procedure with several input parameters, including edge cases.

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

Stored Procedure is one of the important features that improve performance and security by allowing precompiled SQL code to run on the server, reducing network traffic, and limiting direct access to data. It also helps in simplifying complex operations and reducing redundancy by using parameters and error handling. Mastering them is useful for writing efficient and scalable database applications. With centralised control, they make debugging and future enhancements easier. In this blog, you have gained knowledge on stored procedures in detail.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions, prepared by industry experts.