Stored procedures:

A stored procedure is a reusable SQL script that executes a task. Here, we’ll create one to insert new employees without writing INSERT manually each time.

CREATE PROCEDURE AddEmployee(
    IN emp_id INT,
    IN emp_name VARCHAR(100),
    IN emp_age INT,
    IN dept_id INT
)
BEGIN
    INSERT INTO employees (id, name, age, department_id) 
    VALUES (emp_id, emp_name, emp_age, dept_id);
END $$

DELIMITER ;

Calling a procedure:

CALL AddEmployee(101, 'John Doe', 29, 1);
CALL AddEmployee(102, 'Jane Smith', 34, 2);

Dropping a procedure:

DROP PROCEDURE IF EXISTS AddEmployee;

Functions: A function returns a value. We’ll create one to count employees in a department.

-- function
DELIMITER $$

CREATE FUNCTION CountEmployees(dept_id INT) 
RETURNS INT 
DETERMINISTIC
BEGIN
    DECLARE emp_count INT;
    SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id;
    RETURN emp_count;
END $$

DELIMITER ;

Calling a function:

SELECT CountEmployees(1);  -- Returns number of employees in department 1
SELECT CountEmployees(2);  -- Returns number of employees in department 2

Stored Procedures simplify inserting data Functions make querying faster & reusable