Open or not open, that is the stupid question.

This is my stored procedure template: -- myparent_block:BEGIN   -- declare variables   DECLARE fetch_id INT DEFAULT NULL;   DECLARE loop_done BOOLEAN DEFAULT FALSE;   -- declare conditions   DECLARE condition_not_found CONDITION FOR SQLSTATE '02000';   -- declare cursors   DECLARE cursor_myparent CURSOR FOR     SELECT id FROM myparent WHERE serialnumber=sn;   -- declare handlers   DECLARE continue HANDLER FOR condition_not_found     SET loop_done := TRUE;   OPEN cursor_myparent;   REPEAT     FETCH cursor_myparent into fetch_id;     DELETE FROM mychild WHERE pid=fetch_id;   UNTIL loop_done END REPEAT;   CLOSE cursor_device;   DELETE FROM myparent WHERE fkid=fetchfkid; END myparent_block; -- Wish this helps. regards, Stanley Huang

FYI. [SQL] delimiter // drop function if exists stanley.getSQL// create function stanley.getSQL(name varchar(32)) returns text begin   return concat('Hello World, ',name,'!'); end// delimiter ; [mysql client] mysql> select getSQL('stanley'); +-----------------------+ | getSQL('stanley')     | +-----------------------+ | Hello World, stanley! | +-----------------------+ 1 row in set (0.00 sec)   mysql> select length(getSQL('stanley')); +---------------------------+ | length(getSQL('stanley')) | +---------------------------+ |                        21 | +---------------------------+ 1 row in set (0.00 sec) [SQL] delimiter // drop procedure if exists stanley.setParam// create procedure stanley.setParam(in id int, inout word text) begin   if id > 0 then     set word=concat(word, '> 0'); ...

In MySQL stored procedure, we use use "DECLARE" statement to define some variables, like: Local Variable, Conditions and handlers and Cursors. Something you should to take care is about the sequence of these variables declaration. The correct sequence is: 1. Variables 2. Conditions 3. Cursors 4. Handlers IF the sequence is not correct, you will get a error message while building stored procedure. The example for these variables declaration as below: 1. Variables: mysql> DECLARE no int; 2. Conditions mysql> DECLARE co CONDITION FOR SQLSTATE '23000'; 3. Cursors mysql> DECLARE cu CURSOR FOR SELECT * FROM MYSQL.USER; 4. Handlers mysql> DECLARE CONTINUE HANDLER FOR co BEGIN END; -- the empty between BEGIN and END means to ignore the handler, do nothing. Wish this helps. regards, Stanley Huang

MySQL Stored Procedure has two major characters, "SQL SECURITY" and "Data Use". "SQL SECURITY" defines what's the effect user while procedure is called, and "Data Use" indicates the procedure R/W. The detail information as the following: 1. SQL SECURITY:   a. DEFINER-The routine will have the privileges of the user who created it.   b. INVOKER-The routine will have the privileges of ther user who run it.   If you want to change the character, use the command as below:   mysql> ALTER PROCEDURE procedure_name SQL SECURITY [DEFINER/INVOKER]; 2. Data Use:   a. CONTAONS SQL-The option indicates that the routine has the SQL statement but it does not have read or write data.   b. NO SQL-The option indicates that the routine does not have SQL statement.   c. READS SQL DATA-The option indicates that the routine has the SQL statement with read data only.   d. MODIFIES SQL DATA-The option indicates that the routine has the SQL statement wi...