Stored Procedure Unable to Execute With Variable Table Name

Help Procedural SQL
1

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`
2

qry = CONCAT('SELECT COUNT(*) FROM ', srcTbl); will get the result you expect.

The trailing ; is not part of the SQL string. It’s the delimiter to say end of the SQL and in this case not needed.

2 Likes
3

Thank you so much! This works for me :slight_smile: