Re: mysql drawback - stored procedure default parameters not allowed

On Fri, Dec 13, 2019 at 11:53 AM Tim Fathers <X700-IX2J@xxxxxxxxxxx> wrote:

I don't know how you're calling your routines but maybe a workaround

might be to use a single JSON object parameter which contains your actual
parameters and then pull them out of that?

Something along these lines:

create or replace procedure MY_SP(
in PARMS json
)

begin

declare MY_PARM1 varchar(256);
declare MY_PARM2 varchar(256);

set MY_PARM1 = coalese(json_unquote(json_extract(PARMS , '$.myparm1')),

'default1');

set MY_PARM2 = coalese(json_unquote(json_extract(PARMS , '$.myparm2')),

'default2');

: : : : : : : :
end

call MY_SP(json_object('myparm1', 'Hello World!'))

this looks useful. The pattern I follow is to have a stored procedure for
each table in my application. cusms_select( ), orderHeader_select( ) ...
Then the input parms are used when selecting rows from the table. Which
works ok when only a few parms are used. Using a single JSON parameter
makes it less intrusive to add an additional WHERE parameter.