How to Execute Dynamic Sql String as a Query in mysql server?

Question

I have create a trigger which is create a dynamic query.and execute it i had tried 'EXECU q' but it does not work. how can i run/execute that dynamic query.

BEGIN
    DECLARE a INT Default 0 ;
    DECLARE str  VARCHAR(255);
    DECLARE q VARCHAR(500);

    SET q = 'insert into '+new.master_name+' values(';

    simple_loop: LOOP
        SET a=a+1; 
        SET str = SPLIT_STRING(new.remarks,"|",a); 
        SET q = CONCAT(q,str+',');
        SET q = LEFT(q, LENGTH(q) - 1);
        IF str='' THEN
                LEAVE simple_loop; 
            END IF;


    END LOOP simple_loop; 
    SET q = CONCATE(q,');');

    EXEC q
END

This is Trigerr this is Function which i made RETURN REPLACE( SUBSTRING( SUBSTRING_INDEX(str , delim , pos) , CHAR_LENGTH( SUBSTRING_INDEX(str , delim , pos - 1) ) + 1 ) , delim , '' )

You should add the split_string function (which I don't think works) — P.Salmon
– P.Salmon, Commented May 29, 2018 at 10:04
That's not going to work dynamic sql is not allowed in a trigger or stored function. — P.Salmon
– P.Salmon, Commented May 29, 2018 at 11:36

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

I've written a stored procedure to execute dynamically constructed sql statements.

Usage

SET @index := 7;
CALL eval(CONCAT('SELECT ', @index));

Implementation

DELIMITER $$

CREATE PROCEDURE eval(IN dynamic_statement TEXT)
  BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END$$

DELIMITER ;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 20, 2018 at 10:37

Nae

15.5k8 gold badges62 silver badges89 bronze badges

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

Comments

fyaa · Accepted Answer · 2018-05-29 09:31:17Z

0

From my understanding you must make a prepared statement from your string first in order to execute it. So the following partial code should work in replacement for just EXEC q:

PREPARE thequery FROM q;
EXECUTE thequery;

answered May 29, 2018 at 9:31

fyaa

6761 gold badge8 silver badges28 bronze badges

1 Comment

Abhishek Patel Over a year ago

i have tried this also but it does not work. give error at PREPARE thequery FROM q;

Ankit Agrawal · Accepted Answer · 2018-05-29 09:43:11Z

0

use prepare statement to execute your dynamic query

BEGIN
    DECLARE a INT Default 0 ;
    DECLARE str  VARCHAR(255);
    DECLARE q VARCHAR(500);
    DECLARE q1 VARCHAR(500);
    DECLARE q2 VARCHAR(500);

    SET @q = 'insert into '+new.master_name+' values(';

    simple_loop: LOOP
        SET a=a+1; 
        SET str = SPLIT_STRING(new.remarks,"|",a); 
        SET q = CONCAT(@q,str+',');
        SET q1 = LEFT(q, LENGTH(q) - 1);
        IF str='' THEN
                LEAVE simple_loop; 
            END IF;


    END LOOP simple_loop; 

    SET q2 = CONCATE(q1,');');
    PREPARE stmt FROM q2;
    EXECecute stmt;
    deallocate PREPARE stmt;
END

edited May 29, 2018 at 9:43

answered May 29, 2018 at 9:32

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

4 Comments

Abhishek Patel Over a year ago

is dynamic sql is work in trigger or store producer ?

Ankit Agrawal Over a year ago

i have made changes ...try them

Abhishek Patel Over a year ago

Still its will not allow at line PREPARE stmt FRom q2

Abhishek Patel Over a year ago

What the meaning of @ notation? when we use it

Collectives™ on Stack Overflow

How to Execute Dynamic Sql String as a Query in mysql server?

3 Answers 3

Usage

Implementation

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Usage

Implementation

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related