Example

Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.

   CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2))
        LANGUAGE SQL 
        DYNAMIC RESULT SETS 1
   BEGIN 
        DECLARE v_numRecords INTEGER DEFAULT 1;
        DECLARE v_counter INTEGER DEFAULT 0;
        DECLARE c1 CURSOR FOR 
           SELECT salary 
              FROM staff 
              ORDER BY salary;
        DECLARE c2 CURSOR WITH RETURN FOR 
           SELECT name, job, salary 
              FROM staff 
              WHERE salary > medianSalary
              ORDER BY salary;
        DECLARE EXIT HANDLER FOR NOT FOUND
           SET medianSalary = 6666; 
        SET medianSalary = 0;
        SELECT COUNT(*) INTO v_numRecords FROM STAFF;
        OPEN c1;
        WHILE v_counter < (v_numRecords / 2 + 1) 
           DO FETCH c1 INTO medianSalary;
           SET v_counter = v_counter + 1;
        END WHILE;
        CLOSE c1;
        OPEN c2;
   END