Post
Ask Question

Using Stored Procedures in MySQL Enhanced Code Performance

Stored Procedures

Stored Procedures provide a way to encapsulate frequently used or complex SQL Logic statements and allow them to be executed with a single call. It can be called on-demand with or without parameters. It can accept input parameters, perform operations, and return results to the caller.

Key Characteristics of SQL Procedures

  1. Name: Each SQL procedure has a unique name by which it can be called
  2. Input Parameters: Procedures can accept input parameters that allow dynamic data to be passed into the procedure when it is called.
  3. SQL Statements: Procedures contain one or more SQL statements, including queries, data manipulation statements (INSERT, UPDATE, DELETE), and data definition statements (CREATE, DROP).
  4. Procedural Logic: In addition to SQL statements, procedures can include procedural logic such as conditional statements (IF-ELSE), looping constructs (WHILE, FOR), and exception handling.
  5. Return Values: Procedures can return values to the caller.

Merits of SQL Procedures

Merits of SQL Procedures

Create a Stored Procedure

To begin, establish a table named Employees in your database, containing fields like Employee_id, First_name, Last_name, Salary, and department_id. Insert data using the insert query. Next, craft a procedure to retrieve all data from this table. This way, you can call the procedure anytime without rewriting the entire query repeatedly.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT
);

insert into employees values (1, 'Gaurav','Gupta',678907,121),
(2, 'Gayatri','Shukla',60000,121),
(3, 'vani','Gupta',578907,121),
(4, 'ravi','Gupta',478907,122),
(5, 'Gauri','tavan',888907,122),
(6, 'avni','Gupta',778907,123);


DELIMITER //
CREATE PROCEDURE us_cust()
BEGIN
SELECT *
FROM employees;
END //
DELIMITER ;

CALL us_cust(); // Calling Procedure after it's creation

The coder can utilize it whenever needed.

Create a Parametrized Procedure

Specify the parameters using parentheses ().

DELIMITER //
CREATE PROCEDURE ctr_cust(id int)
BEGIN
SELECT * from employees
WHERE employee_id= id;
END //
DELIMITER ;

call ctr_cust(2);

Here, I have passed an input parameter id of datatype int and the SQL statement finds out that the employee whose id is passed as an argument.

Create a Procedure using Loops

A Stored Procedure with an IF loop in MySQL

DELIMITER //
CREATE PROCEDURE CheckSalary(emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    
    SELECT Salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
    
    IF emp_salary > 50000 THEN
        SELECT 'High Salary' AS Status;
    ELSE
        SELECT 'Low Salary' AS Status;
    END IF;
END //
DELIMITER ;

Call CheckSalary(3);

In this stored procedure

  • CheckSalary is the name of the procedure.
  • It takes an employee ID as input parameter.
  • It retrieves the salary of the employee using the input ID.
  • It then checks if the salary is greater than 50000 using an IF loop.
  • If the salary is greater, it returns 'High Salary', otherwise 'Low Salary'.

Call this procedure with the employee ID as an argument, and it will check and return the salary status.

I hope it is clear to all of You. Happy Coding !


Similar Articles