[SOLVED] SingleStore Stored Procedure Unable to Execute With Variable Table Name

SingleStore Stored Procedure Unable to Execute With Variable Table Name

I am trying to get the count of rows into a variable, withing a stored procedure.. the below one WORKS:

DELIMITER //
CREATE OR REPLACE PROCEDURE tmp_into_var () AS
DECLARE
  row_c INT; qry VARCHAR(50);
BEGIN
  qry = "SELECT COUNT(*) FROM myTbl";
  EXECUTE IMMEDIATE qry INTO row_c;
  ECHO SELECT row_c AS "Row Count";
END //
DELIMITER ;

CALL tmp_into_var();

But, when I want to pass the table name as a variable, this FAILS

DELIMITER //
CREATE OR REPLACE PROCEDURE tmp_into_var (
    srcTbl VARCHAR(128)
) AS
DECLARE
  row_c INT; qry VARCHAR(50);
BEGIN
  qry = CONCAT('SELECT COUNT(*) FROM ', srcTbl, ';');
  EXECUTE IMMEDIATE qry INTO row_c;
  ECHO SELECT row_c AS "Row Count";
END //
DELIMITER ;

CALL tmp_into_var("myTbl");

The error being:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Unhandled exception Type: ER_MEMSQL_FEATURE_LOCKDOWN (1706) Message: Feature 'Non-ECHO SELECT inside stored procedure as statement' is not supported by SingleStore. Callstack: #0 Line 6 in `rs_tmp`.`tmp_into_var`

Solution

I got the answer from my question on Singlestore forum:

Just replace the line

qry = CONCAT('SELECT COUNT(*) FROM ', srcTbl, ';');

with

qry = CONCAT('SELECT COUNT(*) FROM ', srcTbl);

The semicolon ; appended at the end was preventing the INTO clause from being executed.

Answer link on SingleStore Forum - here