Unable to run a stored procedure in snowflake database

Question

CREATE OR REPLACE PROCEDURE wh.sp_schema.my_sp(arg1 STRING, arg2 STRING)
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
AS 
$$
 stmt = snowflake.createStatement(
  {sqlText: `CREATE OR REPLACE TABLE wh.table_schema.RAW_`+arg2+`_`+arg1+` LIKE wh.temp_schema.RAW_`+arg2+`_TEMPLATE`}
  );
rs = stmt.execute();
rs.next();
output = rs.getColumnValue(1);
return output;
$$
;

when i create the above stored procedure - below message is displayed

Function my_sp successfully created.

when i run the stored procedure from the worksheet using

CALL my_sp('2018','abc');

I see the below error:

SQL compilation error: Invalid identifier my_sp

I even tried running using fully qualified name for the stored procedure:

CALL wh.sp_schema.my_sp('2018','abc');

I still see the below error:

SQL compilation error: Invalid identifier my_sp.

Also, i would like to know a command to see if my stored procedure has been created or not. I've looked in the snowflake UI, under 'wh" warehouse and "sp_schema" schema, but could not see anything(stored procedures) other than tables.

Answer 1

This is a simple case of a grants issue. As SECURITYADMIN, if you apply the necessary grants to the role you're using,

GRANT USAGE ON DATABASE wh TO ROLE <role>;
GRANT USAGE ON SCHEMA wh.sp_schema TO ROLE <role>;
GRANT USAGE ON ALL PROCEDURES IN SCHEMA wh.sp_schema TO ROLE <role>;

you'll get past this ambiquous error and get to the next real error

JavaScript execution error: Uncaught ReferenceError: arg2 is not defined in MY_SP

Which is due to javascript SP parameters being case sensitive, so you need to modify the code as follows (change arg to ARG):

{sqlText: `CREATE OR REPLACE TABLE wh.table_schema.RAW_`+ARG2+`_`+ARG1+` LIKE wh.temp_schema.RAW_`+ARG2+`_TEMPLATE`}

Answer 2

Snowflake does not support Stored Procedures. They are implementingjavascript` based APIs for it, but it's still in draft mode.

Here's the link to their APIs (Note Draft Document is written on right hand side).

and here's the link to a ticket on their support forum where they've confirmed it.

Answer 3

There are two obvious possibilities why this may go wrong:

1. You've implicitly donated the procedure ownership to a ROLE you don't have
2. There is now a mismatch between the procedure and call type signatures. Verify that there is a wh.sp_schema.my_sp(STRING, STRING) and that your call is like
   CALL wh.sp_schema.my_sp('2018'::STRING,'abc'::STRING);

Answer 4

Try to solve the error by following query syntax.

CALL database_name.schema.MY_SP('database_name','schema');