Declaring QUERY type in BEGIN of stored procedure

Off-Topic
1

I’m creating a stored procedure in memsql and specifically trying to declare a QUERY type variable in the BEGIN section of the procedure_body of the procedure. The CREATE PROCEDURE doc (SingleStoreDB Cloud · SingleStore Documentation) says I should be able to assign a query variable within the procedure body.

It explicitly states “SELECT statements may be used if assigned to a query type variable”, however I receive a sql syntax error when trying to assign a query variable to a select statement within the BEGIN and END of the procedure_body. I can assign the query just fine in the DECLARE section.

A code example:

DELIMITER //

create procedure foo(b int ) AS

DECLARE

a query(x int ) = select 1;

BEGIN

END //

DELIMITER ; ← This code passes

DELIMITER //

create procedure foo(b int ) AS

BEGIN

a query(x int ) = select 1;

END //

DELIMITER ; <— This code fails

2

You have to declare the query type variable in a DECLARE block. Then you can assign to it in the body, like this:

DELIMITER //
create procedure foo(b int ) AS
declare a query(x int);
BEGIN
  a = select 1;
  echo select x from a;
END //
DELIMITER ; 

memsql> call foo(0);
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.03 sec)