MySQL Stored Procedure Programming

Name

DAT-04: Remove unused variables and code

Synopsis

You should go through your programs and remove any part of your code that is no longer used. This is a relatively straightforward process for variables and named constants. Simply execute searches for a variable’s name in that variable’s scope. If you find that the only place it appears is in its declaration, delete the declaration and, by doing so, delete one more potential question mark from your code.

There is never a better time to review all the steps you took, and to understand the reasons you took them, than immediately upon completion of your program. If you wait, you will find it particularly difficult to remember those parts of the program that were needed at one point but were rendered unnecessary in the end. “Dead zones” in your code become sources of deep insecurity for maintenance programmers.

Example

The following block of code has several dead zones that could cause a variety of problems. Can you find them all?

 CREATE PROCEDURE weekly_check ( in_isbn VARCHAR(20), in_author VARCHAR(60) ) BEGIN DECLARE v_count INT; DECLARE v_counter INT; DECLARE v_available INT; DECLARE v_new_location INT DEFAULT 1056; DECLARE v_published_date DATE DEFAULT NOW( ); SET v_published_date=book_published_date(in_isbn); IF DATE_SUB(NOW( ), INTERVAL 60 DAY) > v_published_date THEN CALL review_usage( ); ELSEIF DATE_SUB(NOW( ), INTERVAL 24 DAY) > v_published_date THEN CALL check_availability (in_isbn, v_available, v_count); IF v_available AND ...