SQL Stored Procedures

---

Stored procedures are one of the most powerful features of SQL. They allow developers to encapsulate complex SQL logic into reusable and efficient database operations. A stored procedure is essentially a precompiled collection of SQL statements that can be executed on the database server. By using stored procedures, you can simplify code, improve performance, and enhance security.

---

1. What are SQL Stored Procedures?

A SQL stored procedure is a set of SQL statements that are stored and executed on the database server. It can take parameters, execute SQL queries, and return results or manipulate data. Stored procedures can be invoked by the user, applications, or other database objects (such as triggers or events).

Benefits of Stored Procedures:

• Reusability: Once created, stored procedures can be executed multiple times without re-writing the SQL code.
• Performance: Stored procedures are precompiled, which can improve performance by reducing parsing time.
• Security: Users can be granted permission to execute a stored procedure without giving direct access to the underlying tables.
• Maintainability: Complex logic can be encapsulated in stored procedures, making it easier to update and maintain.

---

2. Syntax for Creating a Stored Procedure

To create a stored procedure, you use the CREATE PROCEDURE statement. The basic syntax looks like this:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements go here
END;

For example, let’s create a simple stored procedure that selects all records from a table called employees:

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM employees;
END;

This stored procedure can now be called to retrieve all employee records.

---

3. Parameters in Stored Procedures

You can define input, output, and input-output parameters for stored procedures. These parameters allow you to pass data to and from the procedure.

3.1 Input Parameters

Input parameters are used to pass values into the stored procedure. These values are then used inside the procedure.

Syntax:

CREATE PROCEDURE procedure_name (@parameter_name datatype)
AS
BEGIN
    -- SQL logic using the parameter
END;

Example:

CREATE PROCEDURE GetEmployeeById (@employee_id INT)
AS
BEGIN
    SELECT * FROM employees WHERE employee_id = @employee_id;
END;

In this example, the GetEmployeeById stored procedure takes an employee_id as an input parameter and retrieves the corresponding employee record.

3.2 Output Parameters

Output parameters allow you to return values from the stored procedure. These are useful for returning aggregate values or status indicators.

Syntax:

CREATE PROCEDURE procedure_name (@output_parameter datatype OUTPUT)
AS
BEGIN
    -- SQL logic
    SET @output_parameter = some_value;
END;

Example:

CREATE PROCEDURE GetEmployeeCount (@total_count INT OUTPUT)
AS
BEGIN
    SELECT @total_count = COUNT(*) FROM employees;
END;

Here, the GetEmployeeCount procedure calculates the number of employees and returns the result through the output parameter @total_count.

3.3 Input/Output Parameters

Input/Output parameters can both accept values and return results.

Syntax:

CREATE PROCEDURE procedure_name (@param1 datatype, @param2 datatype OUTPUT)
AS
BEGIN
    -- SQL logic
    SET @param2 = some_computation;
END;

---

4. Executing Stored Procedures

Once a stored procedure is created, you can execute it using the EXEC or EXECUTE command.

EXEC procedure_name;

If the stored procedure has parameters, you need to pass the appropriate arguments when calling it:

Example:

EXEC GetEmployeeById @employee_id = 5;

If the procedure returns an output parameter, you can capture the result using a variable:

DECLARE @emp_count INT;
EXEC GetEmployeeCount @total_count = @emp_count OUTPUT;
PRINT @emp_count;

---

5. Altering and Dropping Stored Procedures

5.1 Altering a Stored Procedure

If you need to modify a stored procedure, you can use the ALTER PROCEDURE statement. For example, to add a new parameter or change the logic inside the procedure:

ALTER PROCEDURE GetEmployeeById (@employee_id INT, @department_id INT)
AS
BEGIN
    SELECT * FROM employees WHERE employee_id = @employee_id AND department_id = @department_id;
END;

5.2 Dropping a Stored Procedure

To remove a stored procedure from the database, you can use the DROP PROCEDURE statement:

DROP PROCEDURE procedure_name;

---

6. Common Use Cases for Stored Procedures

Stored procedures can be used in a wide range of scenarios. Some common use cases include:

6.1 Data Validation

You can create stored procedures to validate input data before inserting it into the database. For example, checking if an employee already exists before inserting new records.

Example:

CREATE PROCEDURE InsertEmployee (@employee_id INT, @name VARCHAR(100), @salary DECIMAL)
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = @employee_id)
    BEGIN
        INSERT INTO employees (employee_id, name, salary) VALUES (@employee_id, @name, @salary);
    END
    ELSE
    BEGIN
        PRINT 'Employee already exists';
    END
END;

6.2 Complex Business Logic

If your application needs to perform complex calculations or business logic, you can encapsulate this logic within stored procedures. For example, calculating bonuses based on certain criteria.

Example:

CREATE PROCEDURE CalculateEmployeeBonus (@employee_id INT, @bonus DECIMAL OUTPUT)
AS
BEGIN
    DECLARE @salary DECIMAL;
    
    SELECT @salary = salary FROM employees WHERE employee_id = @employee_id;
    
    IF @salary > 50000
        SET @bonus = @salary * 0.10; -- 10% bonus for high earners
    ELSE
        SET @bonus = @salary * 0.05; -- 5% bonus for others
END;

6.3 Transaction Management

Stored procedures can be used to manage transactions, ensuring that complex operations are either fully committed or fully rolled back in case of an error.

Example:

CREATE PROCEDURE TransferFunds (@from_account INT, @to_account INT, @amount DECIMAL)
AS
BEGIN
    BEGIN TRANSACTION;
    
    UPDATE accounts SET balance = balance - @amount WHERE account_id = @from_account;
    UPDATE accounts SET balance = balance + @amount WHERE account_id = @to_account;
    
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT 'Transaction failed';
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION;
        PRINT 'Transaction successful';
    END
END;

 

---

---