Stored Procedures

A Stored Procedure is a set of SQL statements that is stored in the database and can be executed repeatedly. It helps in improving performance, maintaining consistency, and reducing repetitive coding.

Key Advantages

• Reusability: Write once, use multiple times.
• Performance: Executed on the server, reducing network traffic.
• Security: Can restrict direct access to underlying tables.
• Maintainability: Changes are centralized in one place.

2. Creating a Stored Procedure

The basic syntax to create a stored procedure:

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

Example:

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

3. Executing a Stored Procedure

Stored procedures can be executed using the EXEC or EXECUTE command.

Example:

EXEC GetEmployeeByID @EmployeeID = 101;

4. Updating a Stored Procedure

To modify an existing stored procedure, use ALTER PROCEDURE.

Example:

ALTER PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeName, Department
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;

5. Deleting a Stored Procedure

If a stored procedure is no longer needed, you can remove it:

DROP PROCEDURE procedure_name;

Example:

DROP PROCEDURE GetEmployeeByID;

6. Parameters in Stored Procedures

Stored procedures can accept input parameters to filter data or perform operations dynamically.

• Input Parameter: Provides data to the procedure.
• Output Parameter: Returns data back to the caller.

Example of Output Parameter:

CREATE PROCEDURE GetEmployeeCount
    @DeptID INT,
    @EmpCount INT OUTPUT
AS
BEGIN
    SELECT @EmpCount = COUNT(*)
    FROM Employees
    WHERE DepartmentID = @DeptID;
END;

7. Best Practices

• Always use meaningful procedure names.
• Avoid hardcoding values; use parameters.
• Keep procedures modular and focused on a single task.
• Document procedure logic for future reference.
• Test procedures thoroughly before deploying to production.

---