Stored procedure throwing error when value is more than certain character count

Question

BEGIN

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(questions= ''',
questions,
''', yes_no, NULL)) AS ''',
questions,''''
)
) INTO @sql
FROM tbl_main where task=in_task;
SET @sql = CONCAT('SELECT case_id,audited_by,resolved_by, ', @sql, ' FROM  tbl_main WHERE task IN  (''',in_task,''') and audited_date   between(''',from_date,''') and (''',to_date,''') GROUP BY   case_id,audited_by,resolved_by');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

Hi, I'm using the stored procedure and I'm converting row values to column headers dynamically , the stored procedure works fine if the row value is within 50 words or so and it fails when the row value is longer than 50 words . I don't know how to fix it. Can any one help me with this .

Max column header size is 64 characters see dev.mysql.com/doc/refman/5.7/en/identifiers.html — P.Salmon
– P.Salmon, Commented Aug 11, 2017 at 7:29
Looks like an absolute limit but you can try googling mysql max column name length to see if anyone has come up with a workaround you can live with. — P.Salmon
– P.Salmon, Commented Aug 11, 2017 at 8:07
I fixed it by adding below line within procedure and it works fine now . — Lakshmana Babu S
– Lakshmana Babu S, Commented Aug 11, 2017 at 12:45

Lakshmana Babu S · Accepted Answer · 2017-08-11 12:46:28Z

1

set session group_concat_max_len = 5000;

answered Aug 11, 2017 at 12:46

Lakshmana Babu S

516 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure throwing error when value is more than certain character count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related