Difficulty using SCALAR in stored proc

Help Procedural SQL
1

I am trying to create a very basic proc. Eventually, I want to be able to use it to take a table name and a batch size, and loop through and delete tables by the batch size. But, before I can get there, I am having problems even doing something much simpler. I am probably missing something real simple…

Trying to follow example 2 in here: SingleStoreDB Cloud · SingleStore Documentation

CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (4), (5), (6);

DELIMITER //
CREATE or replace PROCEDURE p(tbl VARCHAR(30)) AS
DECLARE

v INT;
BEGIN

v = SCALAR('SELECT MAX(a) FROM ' || tbl, QUERY(a INT));
INSERT INTO output_log VALUES (CONCAT('max value is ',v));

END
//

DELIMITER ;
CALL p('t1');

I run this in MemSQL Studio, and get the following error:

13:47:02: ERROR 1064 ER_PARSE_ERROR: Unhandled exception
Type: ER_PARSE_ERROR
Message: 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 '0' at line 1
Callstack:
  #0 Line 5 in `UNITTEST`.`p`

I am running MemSQL version 6.7.18 and MemSQL Studio version 1.9.6

2

Hi Jason,

I think the issue is your missing:

SET sql_mode = 'PIPES_AS_CONCAT';

That changes the meaning of “||” to string concat. Otherwise, its a logical or.

-Adam

3

You can also do the following (which can be less error prone)

SCALAR(CONCAT('SELECT MAX(a) FROM ', tbl), QUERY(a INT));

instead of

SCALAR('SELECT MAX(a) FROM ’ || tbl, QUERY(a INT));

1 Like
4

Makes perfect sense, sorry I missed that. Thank you both very much for the quick response.