Optimize SQL databases – Stored procedures

2011-03-05

Stored procedures is code in a database similar to a function. Since the code is entered in the database in advance, it can be optimized somewhat before being executed because it does not need to be interpreted on every query. This has several advantages:

The following code shows an example of a stored procedure that performs several different operations:

-- An employee identification consists of the first letter of their name followed by a sequential number.

-- Receives first name, last name and salary as an argument.
CREATE PROCEDURE New_Employee(new_first_name varchar(20), new_last_name varchar(20), new_salary int)
BEGIN
    -- Starting the employee ID number at 0.
    SET @NR = 0;
    -- Get the first letter of the name of the employee.
    SET @CH = (SELECT UPPER(SUBSTR(new_first_name, 1, 1)));
    -- If several employees have the same initial letter, the maximum sequential number of the letter is retrieved.
    IF EXISTS (SELECT nr FROM Employees WHERE name = @CH) THEN
        SET @NR = (SELECT MAX(nr) AS max_nr FROM Employees WHERE name = @CH);
    END IF;

    -- Adjusts the minumum and maximum salary.
    IF (new_salary < 12000) THEN SET new_salary = 12000; END IF;
    IF (new_salary > 35000) THEN SET new_salary = 35000; END IF;
    -- Add a new employee with the next ID number.
    IF (NR < 99) THEN
        INSERT INTO Employees VALUES (@CH, @NR + 1, new_salary, new_first_name, new_last_name);
    END IF;
END;

Stored procedures and function are similar in many ways. Functions are forced to return a value when it is optional for a stored procedure. A function can also only return one value while a stored procedure can return several values if needed. They return values with the use of the RETURN keyword. Functions can also be used in SELECT operations as long as they don’t manipulate any data. Stored procedures can not be used in a SELECT operation, even if it has a RETURN statement.

Both functions and stored procedures can receive multiple parameters. you specify them as a comma separated list in the function or procedure call. They are written in the same way as columns are written in the creation of a table. Exemple: “name varchar(20), age int, …“. A stored procedure is called in the following way:

CALL New_Employee('Hans', 'Westman', 20000);

Other articles in this series: