Stored Proc error when using loop : Multiple statements detected in a single query

Help Procedural SQL
1

Hi ,
I’m getting the follow error when I execute this SP:

call del_truncate_all_project_data() ;

setting_value contains the table name.

singlestore> call del_truncate_all_project_data() ;
±-------------------------------------+
| Status |
±-------------------------------------+
| truncate table: ff_processing_result |
±-------------------------------------+
1 row in set (0.04 sec)

±-------------------------------------+
| v_truncate_table_sql |
±-------------------------------------+
| truncate table ff_processing_result; |
±-------------------------------------+
1 row in set (0.04 sec)

ERROR 1064 (42000): Unhandled exception
Type: ER_PARSE_ERROR (1064)
Message: Leaf Error (127.0.0.1:3307): Multiple statements detected in a single query. truncate table ff_processing_result; TWO_PHASE 17895398464852729182:26
Callstack:
#0 Line 45 in seismos.del_truncate_all_project_data

SP:

CREATE OR REPLACE PROCEDURE del_truncate_all_project_data() RETURNS void AS

– call del_truncate_all_project_data() ;
– SP is called to reset a Project on the Trailer.
– SP should not be present on Production Cloud not should the feature Be present on Production

DECLARE v_cnt_row bigint ;

v_truncate_table_sql text;
v_truncate_table_cmd text=“”;

DECLARE qry QUERY(table_name varchar(100) ) =
select setting_value FROM project_setting
WHERE is_active=1
AND setting_attribute=‘Truncate’ ;

arr ARRAY(RECORD( table_name varchar(100) ));
v_table_name varchar(100) ;

BEGIN

– Total Project
SELECT count(*) into v_cnt_row FROM project p ;

– If there is only 1 project
If v_cnt_row =1 then

    arr = COLLECT(qry);


    FOR x in arr LOOP
    v_table_name = x.table_name ;

    -- drop temp table
    -- ECHO Select concat ('truncate table: ',v_table_name )  as 'Status' ;

    v_truncate_table_sql = CONCAT("truncate table ", v_table_name,';');
    -- v_truncate_table_cmd = CONCAT(v_truncate_table_cmd, v_truncate_table_sql );
    ECHO select v_truncate_table_sql ;
             EXECUTE IMMEDIATE v_truncate_table_sql;
    

    END LOOP;

else

echo Select ‘There is more than 1 project in your database. We cannot truncate all projects related tables.’ as ‘Status’;

end if ;

END//

2

Can you try with the following stored procedure?

delimiter //

CREATE OR REPLACE PROCEDURE del_truncate_all_project_data() RETURNS void AS

-- call del_truncate_all_project_data() ;
-- SP is called to reset a Project on the Trailer.
-- SP should not be present on Production Cloud not should the feature Be present on Production

DECLARE 
	v_cnt_row bigint ;
	v_truncate_table_sql text;
	qry QUERY(table_name varchar(100), table_cmd varchar(100) ) = select setting_value, setting_attribute FROM project_setting WHERE is_active = 1 AND setting_attribute = 'Truncate' ;
	arr ARRAY(RECORD( table_name varchar(100), table_cmd varchar(100) ));
BEGIN

-- Total Project
	SELECT count(*) into v_cnt_row FROM project p;

-- If there is only 1 project
	If v_cnt_row =1 then

	    arr = COLLECT(qry);

	    FOR x in arr LOOP
	    	v_truncate_table_sql = CONCAT(x.table_cmd," table ", x.table_name,';');

	    	EXECUTE IMMEDIATE v_truncate_table_sql;
	    END LOOP;
	else
		echo Select 'There is more than 1 project in your database. We cannot truncate all projects related tables.' as 'Status';
	end if ;

END //

DELIMITER ;