MySQL - Dynamic SQL inside stored procedure

Question

see the procedure InsertHelpQuestion2 below.

DROP PROCEDURE InsertHelpQuestion2;
CREATE PROCEDURE InsertHelpQuestion2(user_id char(50),scenerio_id char(50),component_id char(50))
BEGIN
    DECLARE uu_id char(50);
    SELECT BinToUUID(NewUUID()) INTO uu_id from helpquestion LIMIT 1;
    SET @fields_part = 'INSERT INTO helpquestion(HelpQuestionKey,UserKey';
    SET @values_part = CONCAT(' VALUES(UUIDToBin(\"', uu_id, '\"), UUIDToBin(\"', user_id, '\")');
    IF (scenerio_id) THEN
        SET @fields_part = CONCAT(@fields_part, ', ScenarioKey');
        SET @values_part = CONCAT(@values_part, ', UUIDToBin("', scenerio_id, '")');
    END IF;
    IF (component_id) THEN
        SET @fields_part = CONCAT(@fields_part, ', ComponentKey');
        SET @values_part = CONCAT(@values_part, ', UUIDToBin("', component_id, '")');
    END IF;
    SET @query_full  = CONCAT(@fields_part , @values_part, ';');
    PREPARE STMT FROM @query_full;
    EXECUTE STMT;
END

It has 3 parameters user_id, scenerio_id, component_id All are foreign keys.

NewUUID(), UUIDToBin are stored functions.

As fields have foreign reference i need to write Dynamic sql inside procedure so that the following calls work

CALL InsertHelpQuestion2('F70724DC-AC0D-102D-9C16-00163EEDFCFC', '0F69476A-ABF2-102D-9C16-00163EEDFCFC', '06FFEE04-1FD9-11DF-9B60-001F16F664A9');

CALL InsertHelpQuestion2('F70724DC-AC0D-102D-9C16-00163EEDFCFC', '0F69476A-ABF2-102D-9C16-00163EEDFCFC', '');

CALL InsertHelpQuestion2('F70724DC-AC0D-102D-9C16-00163EEDFCFC', '', '');

But the first call

CALL InsertHelpQuestion2('F70724DC-AC0D-102D-9C16-00163EEDFCFC', '0F69476A-ABF2-102D-9C16-00163EEDFCFC', '06FFEE04-1FD9-11DF-9B60-001F16F664A9');

yeilds the following error

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES(UUIDToBin("69490F3A-6FD3-11DF-964F-F4CE462E9D80"), UUIDToBin("F70724DC-AC' at line 1

Any mistake in string concatenation?

Gary · Accepted Answer · 2010-06-04 13:18:26Z

1

Looks like you may need a closing parenthesis.

SET @query_full = CONCAT(@fields_part , @values_part, ');');

answered Jun 4, 2010 at 13:18

Gary

2,9161 gold badge18 silver badges20 bronze badges

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

1 Comment

Mithun Sreedharan Over a year ago

yup, thats! it should be like SET @query_full = CONCAT(@fields_part, @values_part, ' );');

Andomar · Accepted Answer · 2010-06-04 12:48:40Z

1

Use print @query_full to see the query you're trying to execute. You can copy/paste that in a separate window to debug it.

One obvious error appears to be the escape sequence.

select '\"'
->
\"

answered Jun 4, 2010 at 12:48

Andomar

239k55 gold badges387 silver badges412 bronze badges

2 Comments

Mithun Sreedharan Over a year ago

I tried select @query_full; just before the END of procedure commenting out the PREPARE STMT FROM @query_full; and EXECUTE STMT; And now the call returns @query_full as BLOB but i can't get the value

Mithun Sreedharan Over a year ago

Can you please get me the escape sequence error, i ca'nt figured out it .

Collectives™ on Stack Overflow

MySQL - Dynamic SQL inside stored procedure

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related