MySQL Stored Procedure Not Working as Expected

Help
1

I’m working with a MySQL, self-managed instance v8.0, and I’ve created a stored procedure to retrieve the row counts from various staging tables. However, when I try to execute the stored procedure, I’m getting the following error:

SQL Error [1706] [HY000]: Compilation error in function `Research_HWD`.`Testing` near line 3: Feature 'Non-ECHO SELECT inside stored procedure as statement' is not supported by SingleStore

Here’s the stored procedure code:

DELIMITER $$
CREATE PROCEDURE `Testing`()
RETURNS void
AS
BEGIN
    SELECT 'staging_table_1' AS Table_name, COUNT(*) AS Row_count FROM staging_table_1
    UNION ALL
    SELECT 'staging_table_2' AS Table_name, COUNT(*) AS Row_count FROM staging_table_2
    UNION ALL
    -- Additional UNION ALL statements for other staging tables
    SELECT 'staging_table_n' AS Table_name, COUNT(*) AS Row_count FROM staging_table_n;
END;$$
DELIMITER ;

I’ve tried a few things:

  1. Checked the stored procedure syntax for any errors.
  2. Verified that the staging tables exist and have data.
  3. Tried executing the individual SELECT statements directly, and they work as expected.

However, I’m still unable to get the stored procedure to work correctly, and I’m receiving the error mentioned above. Can anyone help me understand what might be causing this issue? Is there something I’m missing or doing incorrectly when creating or executing the stored procedure in SingleStore? Any assistance or guidance would be greatly appreciated.

2

SELECT statements can’t be executed as it is inside stored procedures. You need to be prefixed with ECHO. Refer to this link ECHO SELECT · SingleStore Documentation

3

It is not working.

I am getting this error.

Error Code: 1064. Compilation error in function `database_name`.`procedure_name` near line 3: 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 'ECHO SELECT 'staging_table_name' AS Table_name, COUNT(*) AS Row'

DELIMITER $$
CREATE PROCEDURE `Testing`()
RETURNS void
AS
BEGIN
    ECHO SELECT 'staging_table_1' AS Table_name, COUNT(*) AS Row_count FROM staging_table_1
    UNION ALL
    ECHO SELECT 'staging_table_2' AS Table_name, COUNT(*) AS Row_count FROM staging_table_2
    UNION ALL
    -- Additional UNION ALL statements for other staging tables
    ECHO SELECT 'staging_table_n' AS Table_name, COUNT(*) AS Row_count FROM staging_table_n;
END;$$
DELIMITER ;
4

ECHO has to apply to a whole top-level SQL SELECT statement. Your 2nd and 3rd ECHO keywords are being applied to subqueries after the UNION ALL operators. Try eliminating those two.

E.g. this works:

singlestore> delimiter //
singlestore> create or replace procedure p() as 
    -> begin
    -> echo select 1 as a union all select 2 as a;
    -> end 
    -> //
Query OK, 1 row affected (0.04 sec)

singlestore> call p();
    -> //
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.07 sec)

Also, if for some reason you don’t want to ECHO a whole rowset back from your stored procedure, you can use SELECT INTO local variables, INSERT…SELECT… into a temp table or regular table, or COLLECT() as a means to get results from your SELECT to perform additional steps with.