SQL Stored Procedures

Stored procedures are one of the core foundations of SQL Server. They enable developers and database administrators to enhance performance, ensure better security, and make managing the database more convenient. The beginner’s guide below will lead the reader from learning about stored procedures to actually mastering them through real-world examples, and stats.

Table of Contents

• What is an SQL Stored Procedure in SQL Server?
• Why Use Stored Procedures in SQL?
• Key Differences between Stored Procedure and Functions
• How to Create Stored Procedure in SQL Server
• Stored Procedure with One Parameter
• Stored Procedure with Multiple Parameters
• Types of SQL Stored Procedures
• Exception Handling in Stored Procedures
• Transaction Management in SQL Stored Procedures
• Dynamic Query Procedures
• How to Pass Multiple Strings as one parameter in Stored procedure
• Optimizing Performance with SQL Stored Procedures
• Real-World Use Cases for Stored Procedures
• Conclusion
• Frequently Asked Questions (FAQs) about Stored Procedures

What is an Stored Procedures in SQL Server?

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements stored in a database. It can be thought of as a reusable unit of logic that can be executed repeatedly to perform specific database operations. Encapsulation of logic, security, and simplification of complex tasks are some benefits provided by stored procedures.

Key Features:

• Security Improvement: Eliminates direct access to database tables.
• Precompiled Execution: This eliminates the overhead of SQL parsing and recompilation, hence increasing performance.
• Code Reusability: Write once and reuse across applications.

Why Use Stored Procedures in SQL?

• Improved Performance: The precompiled procedures run faster than ad-hoc queries. For example, a stored procedure can reduce the execution time up to 30% in transactional systems.
• Improved security: only the stored procedure is accessed instead of the tables.
• Simplified Maintenance: Updating a stored procedure automatically reflects changes across all its usage points.
• Reduced Network Traffic: Only the procedure calls and their results are transferred, and hence, reduces the data load.

Key Differences Between Stored Procedures and User Defined Functions

How to Create Stored Procedure in SQL Server

A stored procedure typically consists of the following:

• Input Parameters: Run dynamically with input parameters.
• Body: It has SQL logic.
• Output Parameters: Optional. Values returned from the procedure.
• Error Handling: Ensures smooth execution even when errors occur.

Stored Procedure with One Parameter

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees_Intellipaat
    WHERE EmployeeID = @EmployeeID;
END;

Execution:

EXEC GetEmployeeDetails @EmployeeID = 101;

Stored Procedure with Multiple Parameters

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10, 2)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Employees_Intellipaat WHERE EmployeeID = @EmployeeID)
    BEGIN
        UPDATE Employees
        SET Salary = @NewSalary
        WHERE EmployeeID = @EmployeeID;
    END
    ELSE
    BEGIN
        PRINT 'Employee does not exist.';
    END
END;

Execution:

EXEC UpdateEmployeeSalary @EmployeeID = 102, @NewSalary = 75000;

Types of SQL Stored Procedures

System Stored Procedures

Built-in procedures for administrative tasks like sp_help (displays object information) or sp_rename (renames database objects).

User-Defined Stored Procedures

Custom procedures created by users to address specific business requirements.

CLR Stored Procedures

Procedures written in .NET languages like C#. Useful for advanced computations or integrations.

Exception Handling in Stored Procedures

CREATE PROCEDURE InsertNewEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Department NVARCHAR(50)
AS
BEGIN TRY
    INSERT INTO Employees_Intellipaat (FirstName, LastName, Department)
    VALUES (@FirstName, @LastName, @Department);
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH;

Execution:

EXEC InsertNewEmployee @FirstName = 'John', @LastName = 'Doe', @Department = 'HR';

Transaction Management in SQL Stored Procedures

Ensure data consistency with BEGIN TRAN, COMMIT, and ROLLBACK.

CREATE PROCEDURE TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        UPDATE Accounts_Intellipaat
        SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccount;

        UPDATE Accounts_Intellipaat
        SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccount;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT ERROR_MESSAGE();
    END CATCH;
END;

Execution:

EXEC TransferFunds @FromAccount = 1, @ToAccount = 2, @Amount = 500.00;

Dynamic Query Procedures

Build flexible procedures with dynamically constructed SQL statements.

CREATE PROCEDURE SearchEmployees
    @SearchTerm NVARCHAR(50)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT * FROM Employees_Intellipaat WHERE FirstName LIKE ''%' + @SearchTerm + '%'' OR LastName LIKE ''%' + @SearchTerm + '%''';
    EXEC sp_executesql @SQL;
END;

Execution:

EXEC SearchEmployees @SearchTerm = 'Smith';

How to Pass Multiple Strings as one parameter in Store procedure

CREATE PROCEDURE GetEmployeesByNames 
@Names NVARCHAR(MAX) -- Comma-separated values 
AS BEGIN 
SET NOCOUNT ON; 
SELECT * FROM Employees_Intellipaat 
WHERE FirstName IN (SELECT value FROM STRING_SPLIT(@Names, ',')); 
END;

Execution:

EXEC GetEmployeesByNames @Names = 'John,David,Michael';

Optimizing Performance with SQL Stored Procedures

• Execution Plan Caching: Do not recompile by using parameterized queries.
• Avoid Over-Indexing: Balance indexes to support procedure queries without overhead.
• Parameter Sniffing: Optimize query plans for parameter values.

Real-World Use Cases for Stored Procedures

• Automating business processes: Examples include daily sales updates.
• Data Validation and Cleanup: Enforce rules before inserting or updating data.
• Precompiled logic: Report generation can be streamlined.
• Bulk Data Operations: Minimize network overhead in ETL workflows.

Conclusion

Mastering stored procedures turns a beginner into an effective SQL developer capable of designing high-performance, secure, and maintainable database systems. Armed with the information provided in this book, you will be empowered to exploit stored procedures in all your projects to their full advantage. Get going by trying out the examples here, one step at a time.

Frequently Asked Questions (FAQs) about Stored Procedures