SQL Stored Procedures

SQL stored procedures are precompiled sets of SQL statements that make database operations faster, more secure, and easier to manage. They help reduce repetitive code, simplify complex tasks, and improve performance by running directly on the server. Stored procedures can accept input and output parameters, making them flexible and reusable across applications. They also help centralize business logic, reduce network traffic, and enhance maintainability. In this blog, we’ll explore what stored procedures are, their types, how to create and use them, and best practices, with examples to help you apply them effectively in real-world scenarios.

Table of Contents:

• What are Stored Procedures in SQL?
• Syntax of SQL Stored Procedure
• When and Why to Use Stored Procedures in SQL
• How do you create a stored procedure in an SQL server?
• Types of SQL Stored Procedures
  1. User-Defined Stored Procedures
  2. System Stored Procedures
  3. Temporary Stored Procedures
  4. Parameterized Stored Procedures
• Stored Procedures vs SQL Functions vs Triggers
• Real-Life Use Case: SQL Stored Procedure Example
• Stored Procedure Best Practices and Common Pitfalls to Avoid
• Conclusion

What are Stored Procedures in SQL?

Stored procedures in SQL Server 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.

Key Features of Stored Procedures in SQL Server

1. 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.
2. Modularity: Stored procedures let you organize related SQL logic into a single reusable package.
3. Reusability: Stored procedures can be executed multiple times without rewriting the SQL code.
4. Performance: Stored procedures run faster because they are precompiled and optimized in the database engine.
5. Security: Access to data can be secured by allowing a user permission to execute a stored procedure, instead of accessing a table directly.

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.

Master SQL for Data Mastery!

Learn SQL from basics to advanced, write powerful queries, and manage databases like a pro. Start your journey today!

Explore Program

Syntax of SQL Stored Procedure

The syntax of SQL Stored Procedure is very simple and easy. Let us explore it below:

Syntax:

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

When and Why 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:

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 one of the types of stored procedures in SQL that 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.

Stored Procedures vs SQL Functions vs Triggers

Real-Life Use Case: SQL Stored Procedure 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');

GO
CREATE PROCEDURE GetBookCountBySubject 
    @SubjectCode INT, 
    @TotalBooks INT OUTPUT 
AS      
BEGIN 
    SELECT @TotalBooks = COUNT(*) FROM LibraryStock WHERE Subject_Code = @SubjectCode; 
END;
GO
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.

Stored Procedure Best Practices and Common Pitfalls to Avoid

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.

Kickstart Your SQL Journey – 100% Free

Structured lessons, real query practice, and solid foundations at zero cost.

Explore Program

Conclusion

A 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.

Check out other related blogs by Intellipaat:

SQL Stored Procedures – FAQs