Error in Create Mysql Procedure or Function

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 ;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x