Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Question Unanswered: Can a function return a temporary table

    Can a MYSQL function return a temporary table that was created inside the same function ?

    Like this :

    DELIMITER $$
    CREATE FUNCTION `getPolicy`(CID SMALLINT, TID SMALLINT)returns
    tt_Rtab TABLE (a INT, i BIT, value VARCHAR(8000))

    BEGIN
    declare V varchar(30);

    CREATE TEMPORARY TABLE IF NOT EXISTS tt_RetTab
    (
    attribute INT,
    inherit BIT,
    `value` VARCHAR(8000)
    );
    DROP TEMPORARY TABLE IF EXISTS tt_Parents;



    END $$

    DELIMITER ;

    -----------------------------------------------

    This can be done in SQL Server DB.
    How to do the same in MYSQL?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Yes it is possible. Try using the PREPARE FROM, EXECUTE and DEALLOCATE PREPARE statements as follows:

    Code:
    delimiter $$
    create procedure sptest()
    begin
    prepare stmt from "CREATE TEMPORARY TABLE IF NOT EXISTS tt_RetTab(attribute int, inherit bit, value varchar(8000))";
    execute stmt;
    deallocate prepare stmt;
    end;
    $$
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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