Guidance needed with using LIKE operator in If-Else block of a Snowflake Stored procedure

Question

I'm trying to create a Snowflake stored procedure, similar to the below code. But the like operator in the if-else block is not working.

Stored procedure

create or replace procedure test_stored_procedure(inputvalue varchar)
returns string not null
language javascript
as 
$$
    if (inputvalue like '%abc%') { return 'valid input' } else { return 'invalid input'}
$$
 ;

I am getting the below error while calling the procedure

JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in test_stored_procedure at ' if (inputvalue like '%abc%') { return 'valid input' } else { return 'invalid input'}' position 14

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2021-07-14T19:12:25

JavaScript equivalent of LIKE '%abc%' is includes('abc'):

create or replace procedure test_stored_procedure(inputvalue VARCHAR)
returns string not null
language javascript
as 
$$
if ( INPUTVALUE.includes('abc')) {
  return 'valid input';
} 
else { 
  return 'invalid input';
}
$$;

Test:

CALL TEST_STORED_PROCEDURE('fabc');
-- valid input

CALL TEST_STORED_PROCEDURE('xyz');
-- invalid input

Notes:

Statements should be terminated with ; and Case-sensitivity in JavaScript Arguments:

For stored procedures (and UDFs) that use JavaScript, identifiers (such as argument names) in the SQL portion of the statement are converted to uppercase automatically (unless you delimit the identifier with double quotes), while argument names in the JavaScript portion will be left in their original case

Guidance needed with using LIKE operator in If-Else block of a Snowflake Stored procedure

1 Answers