(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:
BEGIN
DECLARE 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;
END
SELECT * 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
)
AS
BEGIN
DECLARE 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;
END
IF( "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')));;
call "alteryx.parse_params"(
"params" => ARRAY( ARRAY( 'bla', 'asdasd' ),ARRAY( 'gggg', 'ppppppppsd' ) ),
"key" => 'gggg'
);;
|
Comments
0 comments
Please sign in to leave a comment.