MemSQL OutPut Parameter?

Dears is there is an alternative to out parameter in stored procedure ,
as we need to return Id ( last_insert_id())
return will not suite our scenario

Hi Adel,

We currently don’t have support for IN/OUT/INOUT prefixes to function parameters so function parameters are always pass-by-value in Stored Procedure/User-Defined Functions.

I would like to understand why you can’t use return clause to return the value? Please note, you can use Record datatype (https://docs.memsql.com/v7.1/reference/sql-reference/procedural-sql-reference/record/), which is equivalent to C/C++'s struct, to return multiple scalar values from the Stored Procedure. If this still doesn’t work for you, I would like to know why you can’t use Return clause to return the value of last_insert_id().

could the called SP pass back a query type variable ( with 2 vars) and upon return to the caller then access those two varaibles ?
create callerSP (in1 char(2) ) returns query(out1 char(1) , out2 (char(2) , out3 char(3))
as declare
qry_callerSP query(out1 char(1) , out2 char(2) , out3 char(3));
qry_calledSP_back query(calledSP1 char(1), calledSP2 char(2) ) ;
begin
qry_calledSP_back = calledSP (in1) ; /* it blows up here with ec=1992*/
end

create calledSP (in1 char(2) ) returns query(out1 char(1) , out2 (char(2) )
as
declare
qry_calledSP_out query(var1 char(1), var2 char(2) ) ;
tmp1 char(1); tmp2 char(2);
begin
tmp1 = “a”; tmp2=“bc” ;
qry_calledSP_out = select tmp1, tmp2;
return qry_calledSP_out;
end

Yes, a SP can return query type variable.

I am not sure what your comment “it blows up here with ec=1992” refers to?