(A note: for passing variable number of parameteres into a stored procedure, you can also use the approach described here )
To pass key value pairs to a procedure, you can use multi-dimensional arrays - here’s an example:
BEGINDECLARE object params = ARRAY( ARRAY( 'a', 'b') , ARRAY( 'c', 'd') );DECLARE integer VARIABLES.i = 0;CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" object, "value" object);WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", params[i+1][1] AS "key", params[i+1][2] as "value"; VARIABLES.i = VARIABLES.i + 1;ENDSELECT * from "#__LOCAL__keyvalue_store";END
CREATE VIRTUAL PROCEDURE views.parse_params( IN params object, IN "key" string )RETURNS("argument_number" integer,"key" string,"value" object)ASBEGINDECLARE integer VARIABLES.i = 0;CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" string, "value" object);WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", cast( params[i+1][1] as string ) AS "key", params[i+1][2] as "value"; VARIABLES.i = VARIABLES.i + 1;ENDIF( "key" IS NOT NULL ) SELECT * from "#__LOCAL__keyvalue_store" WHERE "key" = "parse_params.key";ELSE SELECT * from "#__LOCAL__keyvalue_store";END;;call views.parse_params( ARRAY(ARRAY( 'foo', 'bar'),ARRAY( 'baz', 'boz')));; -- with parameter call "alteryx.parse_params"( "params" => ARRAY( ARRAY( 'bla', 'asdasd' ),ARRAY( 'gggg', 'ppppppppsd' ) ), "key" => 'gggg');;