CODEWITHSUNDEEP
mysqlsql
Bharathi
Bharathi

Reputation: 1545

IN Mysql Function how can return more than one row

I am using MySQL.I able get more row in MySQL procedure but not in function . I want to write the function by returning values more than one row . I attach my partial code:

drop function if exists test_splitfn;
DELIMITER $$

CREATE Function test_splitfn( sSepar VARCHAR(255), saVal TEXT )
Returns @MyTable table 
body:

BEGIN

  DROP TEMPORARY TABLE IF EXISTS lib_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    pos int unsigned NOT NULL auto_increment,
    val VARCHAR(255) NOT NULL,
    PRIMARY KEY  (pos)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;

  END WHILE;

     Insert @MyTable
      SELECT val  FROM lib_Explode;


    return @MyTable;
END; $$
DELIMITER ;

Upvotes: 1

Views: 3054

Answers (2)

user1804985
user1804985

Reputation: 297

In MySQL function you can't able to return the table..

Upvotes: 3

Yuriy
Yuriy

Reputation: 1984

Try something like this:

DROP FUNCTION IF EXISTS test_splitfn;
DELIMITER $$

CREATE PROCEDURE test_splitfn (IN sSepar VARCHAR(255), saVal TEXT)
body:BEGIN

    DROP TEMPORARY TABLE IF EXISTS lib_Explode;
    CREATE TEMPORARY TABLE lib_Explode(
        pos int unsigned NOT NULL auto_increment,
        val VARCHAR(255) NOT NULL,
        PRIMARY KEY  (pos)
    ) ENGINE=Memory COMMENT='Explode() results.';

    IF sSepar IS NULL OR saVal IS NULL THEN
        LEAVE body;
    END IF;

    SET @saTail = saVal;
    SET @iSeparLen = LENGTH( sSepar );

    WHILE (@saTail != '') DO
        SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
        SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
        INSERT INTO lib_Explode SET val = @sHead;
    END WHILE;

    SELECT val FROM lib_Explode;
END$$
DELIMITER ;

Upvotes: 0

Related Questions