SQL Stored Procedures

What is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements that can be executed as a single unit. Stored procedures can accept parameters, perform operations, and return results, making them powerful tools for managing database operations.

Benefits of Using Stored Procedures

• Performance: Stored procedures are compiled once and stored in the database, which can enhance performance on subsequent executions.
• Security: Users can be granted permission to execute stored procedures without needing direct access to the underlying tables.
• Code Reusability: Procedures can be reused across different applications or modules, reducing code duplication.
• Maintainability: Changes can be made in one place (the stored procedure) rather than in multiple locations in your application code.

Syntax of Stored Procedures

The basic syntax for creating a stored procedure in SQL is as follows:

CREATE PROCEDURE procedure_name
        @parameter1 datatype,
        @parameter2 datatype
        AS
        BEGIN
            -- SQL statements
        END

For example:

CREATE PROCEDURE GetEmployeeDetails
        @EmployeeID INT
        AS
        BEGIN
            SELECT * FROM Employees WHERE EmployeeID = @EmployeeID
        END

Executing Stored Procedures

Stored procedures can be executed using the following syntax:

EXEC procedure_name @parameter1, @parameter2

For example:

EXEC GetEmployeeDetails @EmployeeID = 1

Example of a Stored Procedure

Here is a simple example of a stored procedure that updates employee information:

CREATE PROCEDURE UpdateEmployeeSalary
        @EmployeeID INT,
        @NewSalary DECIMAL(10, 2)
        AS
        BEGIN
            UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID
        END