1. Data Virtuality
  2. How-To Guides
  3. Cookbook Recipes (Snippets)

Articles in this section

See more

Pass Key Value Pairs to a Procedure Using Multi Dimensional Arrays

Avatar
Julian Jöris
  • Updated
Follow

 

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')));;
 
-- with parameter
 
call "alteryx.parse_params"(
    "params" => ARRAY( ARRAY( 'bla', 'asdasd' ),ARRAY( 'gggg', 'ppppppppsd' ) ),
    "key" => 'gggg'
);; 
 

Related articles

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk