SQL Stored Procedures for SQL Server

Introduction to SQL Server Stored Procedures

What are Stored Procedures?

A stored procedure is a group of SQL statements that are stored and executed on the SQL Server. It is a pre-compiled and reusable piece of code that can be called and executed multiple times. Stored procedures provide a way to encapsulate complex logic and business rules within the database server, promoting code reusability, security, and performance optimization.

Advantages of Using Stored Procedures:

• Code Reusability: Stored procedures can be called from various applications, reducing code duplication and promoting modular design.
• Improved Performance: Stored procedures are pre-compiled and stored in memory, resulting in faster execution compared to ad-hoc SQL queries.
• Enhanced Security: Permissions can be assigned to stored procedures, allowing fine-grained control over database access.
• Easy Maintenance: Modifying a stored procedure affects all applications that use it, simplifying maintenance and ensuring consistent behavior.
• Transaction Management: Stored procedures can be used to group multiple SQL statements into a single transaction, ensuring atomicity and data integrity.

Creating a Stored Procedure:

In SQL Server, you can create a stored procedure using the CREATE PROCEDURE statement. Here's an example of creating a simple stored procedure that retrieves customer details based on an input parameter:

        CREATE PROCEDURE GetCustomerDetails
            @customerId INT
        AS
        BEGIN
            SELECT * FROM Customers
            WHERE CustomerId = @customerId
        END
    

Executing a Stored Procedure:

To execute a stored procedure, you can use the EXECUTE or EXEC keyword followed by the stored procedure name and any input parameters. Here's an example of executing the previously created stored procedure:

        EXECUTE GetCustomerDetails @customerId = 1
    

Modifying a Stored Procedure:

To modify an existing stored procedure, you can use the ALTER PROCEDURE statement. This allows you to add, remove, or modify the SQL statements within the stored procedure. Here's an example of adding an additional filter to the previous stored procedure:

        ALTER PROCEDURE GetCustomerDetails
            @customerId INT,
            @status VARCHAR(20)
        AS
        BEGIN
            SELECT * FROM Customers
            WHERE CustomerId = @customerId
                AND Status = @status
        END
    

Dropping a Stored Procedure:

To remove a stored procedure from the SQL Server, you can use the DROP PROCEDURE statement. This permanently deletes the stored procedure from the database. Here's an example of dropping the previously created stored procedure: