I found a post suggesting to use the cursor to select from a temporary table
that is created dynamically each time
This seems to work...
DECLARE adi CURSOR FOR select count(*) from t;
SET @stmt_text=CONCAT("drop temporary table if exists t");
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @stmt_text=CONCAT("create temporary table t as select i.ident from
",tablename," a join individual i on a.ident=i.ident where fid=",agpfid);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select * from t;
OPEN adi;
FETCH adi INTO resadi;
CLOSE adi;
IF (resadi>0) THEN
select resadi as tablename;
END IF;
On 10/16/08 4:07 PM, "Martin Gainty" <[EMAIL PROTECTED]> wrote:
> Good Afternoon Olaf-
>
> not seeing anything obvious which could be incorrect
> what happens when you execute the proc
> ?
>
> Thanks
> Martin Gainty
> ______________________________________________
> Disclaimer and confidentiality note
> Everything in this e-mail and any attachments relates to the official business
> of Sender. This transmission is of a confidential nature and Sender does not
> endorse distribution to any party other than intended recipient. Sender does
> not necessarily endorse content contained within this transmission.
>
>
>> > Date: Thu, 16 Oct 2008 15:31:23 -0400
>> > Subject: Stored proc - dynamic sql in cursor
>> > From: [EMAIL PROTECTED]
>> > To: mysql@lists.mysql.com
>> >
>> > 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]
>> >
>
>
> Want to read Hotmail messages in Outlook? The Wordsmiths show you how. Learn
> Now
> <http://windowslive.com/connect/post/wedowindowslive.spaces.live.com-Blog-cns!
> 20EE04FBC541789!167.entry?ocid=TXT_TAGLM_WL_hotmail_092008>
-------------------------
Olaf Stein
DBA
Battelle Center for Mathematical Medicine
Nationwide Children's Hospital, The Research Institute
700 Children's Drive
43205 Columbus, OH
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]
³I consider that the golden rule requires that if I like a program I must
share it with other people who like it.²
Richard M. Stallman
