Hi all,
I am running into some issues with what I am trying to do in a stored proc.
Basically I am trying to find records related to certain individuals in
other tables in the databases and if there are any, tell me how many.
Instead of doing this for each of these tables individually I use a cursor:
DECLARE tnames CURSOR FOR select table_name from information_schema.tables
where table_schema='agpv2' and table_name like 'ad%' and table_name not like
'%headers' order by table_name desc;
to get all the tables I need.
Now I loop over the result set of this cursor and want to dynamically insert
the tablename into a second cursor. From what I read (and tried) that does
not work:
OPEN tnames;
REPEAT
FETCH tnames INTO tablename;
IF NOT tnames_done THEN
SELECT tablename;
BEGIN
DECLARE resadi INT;
DECLARE adi_done INT DEFAULT 0;
DECLARE adi CURSOR FOR select count(*) from tablename a join
individual i on a.ident=i.ident where fid=agpfid;
OPEN adi;
FETCH adi INTO resadi;
CLOSE adi;
IF (resadi>0) THEN
select resadi as adi_wps;
END IF;
The cursor does not use tablename as a variable.
What does work is using prepared statements:
SET @stmt_text=CONCAT("select count(*) from ",tablename," a join
individual i on a.ident=i.ident where fid=",agpfid);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The problem with this is that I only want the result of the query if
count(*) > 0 as there are many tables I am looking in and most have no
reference to individual so I do not want them in the output and this just
executes the statement.
Is there any way I can dynamically manipulate the string fro the cursor.
Or, is there anyway I can catch the EXECUTE stmt output and look at it
before outputting it?
Thanks
Olaf
Here is the full proc as I would like it to work:
DELIMITER //
DROP PROCEDURE IF EXISTS show_pheno//
CREATE PROCEDURE show_pheno(agpfid INT)
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'shows phenotypes for given family id'
BEGIN
BEGIN
DECLARE tablename TEXT;
DECLARE tnames_done INT DEFAULT 0;
DECLARE tnames CURSOR FOR select table_name from information_schema.tables
where table_schema='agpv2' and table_name like 'ad%' and table_name not like
'%headers' order by table_name desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET tnames_done=1;
OPEN tnames;
REPEAT
FETCH tnames INTO tablename;
IF NOT tnames_done THEN
SELECT tablename;
BEGIN
DECLARE resadi INT;
DECLARE adi_done INT DEFAULT 0;
SET @tn = tablename;
DECLARE adi CURSOR FOR select count(*) from a join individual i on
a.ident=i.ident where fid=agpfid;
OPEN adi;
FETCH adi INTO resadi;
CLOSE adi;
IF (resadi>0) THEN
select resadi as adi_wps;
END IF;
END;
END IF;
UNTIL tnames_done
END REPEAT;
CLOSE tnames;
END;
END //
----------------------------------------- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
