7.1 Writing and Executing Stored Procedures

7.1.1 Introduction to Stored Procedures

• Definition: Stored procedures are precompiled collections of SQL statements that are stored in a database and executed when called. They help in simplifying repetitive tasks and improving performance by reducing network traffic.
• Purpose: Stored procedures encapsulate complex logic, improve reusability, and ensure security by controlling access to database operations.

7.1.2 Advantages of Stored Procedures

• Reusability: Once created, stored procedures can be executed multiple times, reducing code duplication.
• Improved Performance: As they are precompiled, stored procedures execute faster than dynamic SQL queries.
• Security: Stored procedures can be used to restrict direct access to database tables, providing an additional layer of security.
• Maintainability: Changes to business logic can be made centrally in the stored procedure, making maintenance easier.

7.1.3 Creating a Stored Procedure

• Syntax:

  CREATE PROCEDURE procedure_name
      [parameters]
  AS
  BEGIN
      -- SQL statements
  END;
  

• Parameters: Define input and output parameters. Input parameters accept values, and output parameters return values.
• SQL Logic: Write the SQL queries that the stored procedure will execute.

7.1.4 Example: Simple Stored Procedure

A simple example to retrieve employee details:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT EmployeeName, Department, Salary
    FROM Employee
    WHERE EmployeeID = @EmployeeID;
END;

7.1.5 Executing a Stored Procedure

To execute a stored procedure, use the EXEC or EXECUTE command:

EXEC GetEmployeeDetails @EmployeeID = 5;

• This command runs the GetEmployeeDetails procedure with an input value of 5 for the @EmployeeID parameter.

7.1.6 Example: Stored Procedure with Multiple Parameters

A stored procedure to insert a new employee:

CREATE PROCEDURE InsertEmployee
    @EmployeeName NVARCHAR(100),
    @Department NVARCHAR(50),
    @Salary DECIMAL(10, 2)
AS
BEGIN
    INSERT INTO Employee (EmployeeName, Department, Salary)
    VALUES (@EmployeeName, @Department, @Salary);
END;

• You execute it like this:

EXEC InsertEmployee @EmployeeName = 'John Doe', @Department = 'IT', @Salary = 60000.00;

7.1.7 Stored Procedure with Output Parameters

Stored procedures can return values using output parameters. For example, a stored procedure to get employee count:

CREATE PROCEDURE GetEmployeeCount
    @Department NVARCHAR(50),
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employee
    WHERE Department = @Department;
END;

• To execute it:

DECLARE @EmployeeCount INT;
EXEC GetEmployeeCount @Department = 'HR', @EmployeeCount = @EmployeeCount OUTPUT;
SELECT @EmployeeCount AS 'Number of Employees in HR';

7.1.8 Transaction Control in Stored Procedures

Stored procedures can include transaction control to ensure data consistency:

• COMMIT: Saves changes made in the transaction.
• ROLLBACK: Reverts changes if an error occurs.

Example:

BEGIN TRANSACTION;
    UPDATE Employee
    SET Salary = 70000
    WHERE EmployeeID = 5;
    -- If some error occurs, rollback the transaction
    ROLLBACK;

7.1.9 Error Handling in Stored Procedures

Using TRY and CATCH blocks for error handling:

BEGIN TRY
    -- Code that may throw an error
    UPDATE Employee
    SET Salary = 80000
    WHERE EmployeeID = 5;
END TRY
BEGIN CATCH
    -- Handle errors
    PRINT 'An error occurred';
END CATCH;

Key Points for Writing and Executing Stored Procedures:

• Parameters: Use parameters to pass values into and out of the stored procedure.
• BEGIN/END Block: The body of the procedure is wrapped in the BEGIN and END block to group multiple statements.
• Transaction Control: Stored procedures can include transaction control commands (COMMIT, ROLLBACK) to ensure data consistency and integrity.
• Error Handling: You can include error handling within stored procedures using TRY and CATCH blocks to manage exceptions.

Conclusion:

Stored procedures are a powerful feature of SQL that allow developers to encapsulate business logic and simplify database operations. By writing and executing stored procedures, developers can enhance performance, ensure data integrity, and maintain a secure, reusable code base.