MYSQL - Stored Procedures guidance

Question

I need to write a stored procedure whereby I must find the total time a song has been played on the radio in the last 6 months for each month, so 6 outputs, this month, last month, and so on until 6 months ago. To do this I must used a Track_no's start_airtime & finish_airtime. The total of these will give me the total air time and must be separated by each individual month, and the output should contain two coloumns, firstly the month name, and secondly the total time it has been aired.

Note the start air time and finish air time is the length of the song, but i don't have this information available to use to must derive it from these two values.

My table set up is as follows:

track_no TINYINT(3) UNSIGNED
genre VARCHAR(60)
purchasedate DATE NOT NULL
start_airtime TIME NOT NULL
finish_airtime TIME
artist CHAR(9)

I am completely clueless about stored procedures so any information at all would be grateful. Many thanks.

so far my code is :

DELIMITER //
DROP PROCEDURE IF EXISTS airtime_of_month//
CREATE PROCEDURE airtime_of_month(IN track_input TINYINT) 
BEGIN
        DECLARE x, track_num TINYINT;
        DECLARE totalTime VARCHAR(25);
        SET x = 7;
        SET track_num = track_input;
        WHILE x > 1 DO
                    SET x = x - 1;
                    SELECT track_no INTO track_num
                            FROM Radio
                            WHERE track_input = track_no;
                    IF (track_input IS NULL) THEN
                            SELECT CONCAT(track_input, ' Is Not                               
                                                           A Valid Track Number') as _;
                ELSE 
                       SELECT track_no,   
             CONCAT(FLOOR(HOUR(TIMEDIFF(finish_airtime,start_airtime)) / 24), ' days ',
                 MOD(HOUR(TIMEDIFF(finish_time,start_airtime)), 24), ' hrs ',                                
          MINUTE(TIMEDIFF(finish_airtime,start_airtime)), ' minutes ') AS total_AirTime
                FROM Radio
                WHERE DATE_SUB(MONTH(purchasedate), INTERVAL x   
                                                                               MONTH);
                END IF;
                SELECT track_num;
    END WHILE;
END //

CALL airtime_of_month (2)/ 
DELIMITER ;

However when i do this it does compile but doesn't return anything. Any ideas?

score 1 · Accepted Answer · edited Jan 21 '14 at 14:33

1

Example of Function:

DROP FUNCTION IF EXISTS test;

DELIMITER $$
CREATE FUNCTION test(in_number INT) RETURNS INT
    BEGIN
        DECLARE countTotal INT;
        SET countTotal = SELECT COUNT(*) FROM nGrams;
    RETURN countTotal + in_number;
END $$
DELIMITER ;

Example of Procedure:

DROP PROCEDURE IF EXISTS cron_scrubber;

DELIMITER $$
CREATE PROCEDURE cron_scrubber()
BEGIN

    INSERT INTO driver_car_ready_history (id_driver_car_ready, status, date_register, id_driver_car, id_job) 
    SELECT dcr.id_driver_car_ready, dcr.status, dcr.date_register, dcr.id_driver_car, dcr.id_job 
    FROM driver_car_ready dcr 
    ORDER BY dcr.id_driver_car_ready;

    TRUNCATE TABLE driver_car_ready;

END $$
DELIMITER ;

I hope it helps a little.

edited Jan 21 '14 at 14:33

BenMorel

34,448
50
182
322

answered Dec 02 '12 at 19:49

alditis

4,633
3
49
76

I will add what code i have so far and edited my text above, if you could have a look to see where im going wrong maybe? thanks – user1854392 Dec 02 '12 at 20:58
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20486/discussion-between-alditis-and-user1854392) – alditis Dec 02 '12 at 21:32

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

Firstly, don't use a stored procedure unless you absolutely have to (there are few cases for this by the way).

See this answer for just some reasons why you shouldn't use stored procedures.

This can be done with a fairly simply query, which can be "packaged up" into a view if you want modularity of your SQL code:

create view track_airtime as
select
    track_no, 
    extract(YEAR_MONTH from purchasedate) as year_month, 
    sum(minute(TIMEDIFF(finish_airtime, start_airtime))) as total_airtime
from mytable
where extract(YEAR_MONTH from purchasedate) >= 
      extract(YEAR_MONTH from subdate(now(), interval 6 month))
and track_no = ?
group by 1, 2
order by 2 desc

edited May 23 '17 at 12:12

Community

1
1

answered Dec 02 '12 at 19:22

Bohemian

412,405
93
575
722

I need to use a stored procedure as i need to pass into the procedure the Track_no – user1854392 Dec 02 '12 at 19:25
@user1854392 but you can pass the track no to a query just as easily – Bohemian Dec 02 '12 at 19:28
I need to it to be user friendly, so i don't want them having the go through the code and editing it themselves, instead just to have a parameter where they can just type in which one they want to find. – user1854392 Dec 02 '12 at 19:32
@user1854392 Then make it a view! Views are a great way to externalize sql code from the app code, and they can be reworked without rebuilding your app – Bohemian Dec 02 '12 at 19:33

MYSQL - Stored Procedures guidance

2 Answers2