Error in Create Mysql Procedure or Function-CodePudding

I tried many times to create mysql procedure or function but failed all my tries! This is the SQL statements (run without error as sql query) that I want to make it as procedure or function:

    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(
      CASE WHEN s.key = "', s.key, '" THEN s.value END) 
      AS ',s.key))
    
    INTO @sql
    FROM submissions s where s.formid='kk';
     
     
    SET @sql = CONCAT('SELECT s.formid,s.subid, ', @sql, 
      ' FROM submissions s where formid=''kk'' GROUP BY s.formid,s.subid');
    SELECT @sql;
     
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

Where 'kk' is input variable.

CodePudding user response：

You can add you code to a create procedure with a DELIMITER. a function will not work, as you return a table and MySQL doesn't support tables as datatype

DELIMITER §§
CREATE PROCEDURE proc1()
BEGIN
    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT CONCAT(
      'MAX(
      CASE WHEN s.key = "', s.key, '" THEN s.value END) 
      AS ',s.key))
    
    INTO @sql
    FROM submissions s where s.formid='kk';
     
     
    SET @sql = CONCAT('SELECT s.formid,s.subid, ', @sql, 
      ' FROM submissions s where formid=''kk'' GROUP BY s.formid,s.subid');
    SELECT @sql;
     

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END §§
DELIMITER ;