Using "Call" with additional steps in a stored procedure


#1

Quick question about using “Call” inside a stored procedure. If I use a “Call” and then have additional steps in the procedure following the “Call”, does the Procedure wait for the initial “Call” to complete it’s transaction before the next step? Or doesit simply throw the “Call” out into the wind and move on?


#2

All statements inside stored procedures are blocking. So when you call ‘Call’ or any other statement, Memsql waits for the statement to execute before moving on.

This is necessary because the stored procedure called through ‘Call’ statement might have side-effects on the following statement. (Statements are blocking even if they don’t have dependencies).

Example:

>create table t(a int);
Query OK, 0 rows affected (0.08 sec)
>delimiter //
> create procedure sp_1() as begin insert into t values (1); end;//
Query OK, 1 row affected (0.00 sec)
> create procedure sp_2() as begin call sp_1(); echo select * from t; end;//
Query OK, 1 row affected (0.00 sec)
> delimiter ;
> call sp_2();
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.15 sec)
Query OK, 0 rows affected (0.15 sec)

AFAIK we don’t have support yet for asynchronously launching queries.


#3

Great answer! Thanks. I have a procedure calling others and I wanted the secondary procedures to write to a temp table in json and return the output without using EXECUTE IMMEDIATE.

Let us know when you support asynchronous calls please. I would prefer if I could call my logging without impacting the main transaction.