Professional Web Applications Themes

StoredProc: How to pass 'table' as argument ? - MySQL

I have such StoredProc (Function) Is it possible to pass 'Table' to stored proc as argument? code ----------------------------- CREATE FUNCTION `DDT`.`GetSelectMd5`() RETURNS varchar(255) BEGIN DECLARE done INT DEFAULT 0; DECLARE row_crc32 INT; DECLARE column_concat longtext; DECLARE column_md5 varchar (64); DECLARE reader CURSOR FOR SELECT CRC32_DATA FROM Table; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN reader; SET column_concat = ''; WHILE not done DO FETCH reader INTO row_crc32; SET column_concat = concat(column_concat,row_crc32); end while; CLOSE reader; set column_md5 = md5(column_concat); RETURN md5(column_md5); END ----------------- How to use TableName from variable inside CURSOR FOR SELECT ...FROM 'TableName'?...

  1. #1

    Default StoredProc: How to pass 'table' as argument ?

    I have such StoredProc (Function)
    Is it possible to pass 'Table' to stored proc as argument?

    code
    -----------------------------
    CREATE FUNCTION `DDT`.`GetSelectMd5`() RETURNS varchar(255)
    BEGIN
    DECLARE done INT DEFAULT 0;

    DECLARE row_crc32 INT;
    DECLARE column_concat longtext;
    DECLARE column_md5 varchar (64);
    DECLARE reader CURSOR FOR SELECT CRC32_DATA FROM Table;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN reader;
    SET column_concat = '';
    WHILE not done DO
    FETCH reader INTO row_crc32;
    SET column_concat = concat(column_concat,row_crc32);
    end while;
    CLOSE reader;
    set column_md5 = md5(column_concat);
    RETURN md5(column_md5);
    END
    -----------------

    How to use TableName from variable inside CURSOR FOR SELECT ...FROM
    'TableName'?



    Artur Bać Guest

  2. #2

    Default Re: StoredProc: How to pass 'table' as argument ?

    Artur Bać wrote:
    > I have such StoredProc (Function)
    > Is it possible to pass 'Table' to stored proc as argument?
    >
    > code
    > -----------------------------
    > CREATE FUNCTION `DDT`.`GetSelectMd5`() RETURNS varchar(255)
    > BEGIN
    > DECLARE done INT DEFAULT 0;
    >
    > DECLARE row_crc32 INT;
    > DECLARE column_concat longtext;
    > DECLARE column_md5 varchar (64);
    > DECLARE reader CURSOR FOR SELECT CRC32_DATA FROM Table;
    > DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    > OPEN reader;
    > SET column_concat = '';
    > WHILE not done DO
    > FETCH reader INTO row_crc32;
    > SET column_concat = concat(column_concat,row_crc32);
    > end while;
    > CLOSE reader;
    > set column_md5 = md5(column_concat);
    > RETURN md5(column_md5);
    > END
    > -----------------
    >
    > How to use TableName from variable inside CURSOR FOR SELECT ...FROM
    > 'TableName'?
    >
    >
    >
    Unfortunately, with the current implementation of cursors, you can't.
    You need to know the table name in advance if you want to use cursors.
    A variable table name means that you should use dynamic SQL (server side
    prepared statements), but they are not currently supported with cursors.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: StoredProc: How to pass 'table' as argument ?

    thanks for answer.
    I Solved this in a little different way
    with Temporary Table Engine=Memory

    So the table name is static 'temp'
    before execution of function i dynamicaly create table in memory then
    execute function on temp and at the end i drop the table
    so i dont have to pass a table to function.
    But programing this way is a global variable programing like in visual basic
    ....
    Could lead to errors in recursive functions ....
    It is pretty hard for person how use OO programing only switch back to
    procedural programing in SQL Stored Proc....

    drop table if exists temp;
    CREATE temporary TABLE temp ENGINE=Memory
    Select md5(concat(
    CASE WHEN Indeks NOT LIKE '' THEN Indeks ELSE '' END,
    ..................................
    )) AS CRC32_DATA From CountryE order by CRC32_DATA asc;
    Select GetSelectMd5() as MD5;
    drop table if exists temp;



    Artur Bac Guest

Similar Threads

  1. Loop a Result Set Within a StoredProc
    By Warden in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: June 30th, 10:54 PM
  2. Timing Out StoredProc's
    By ssri-stew in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 17th, 03:45 PM
  3. Which is better, pass, or not pass by reference?
    By Randell D. in forum PHP Development
    Replies: 6
    Last Post: October 21st, 09:24 PM
  4. Replies: 0
    Last Post: September 7th, 06:53 PM
  5. Replies: 2
    Last Post: August 12th, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139