Call Snowflake Procedure from Azure Function App

Question

I have procedure in Snowflake and would like to call it from my Timer Triggered Azure Function App.
That procedure expects a parameter which is of type string. Following is my code snippet to connect to Snowflake and calling that procedure with parameter.

using (IDbConnection conn = new SnowflakeDbConnection())
{
    //Connect to Snowflake
    conn.ConnectionString = Environment.GetEnvironmentVariable("SnowflakeConnection");
    conn.Open();
    using (IDbCommand cmd = conn.CreateCommand())
    {
        if (conn.State == ConnectionState.Open)
        {
            cmd.CommandText = "SP_Snowflake_Procedure";
            //cmd.CommandType = CommandType.StoredProcedure;
            var date = cmd.CreateParameter();
            date.ParameterName = "RUNDATE";
            date.DbType = DbType.String;
            date.Value = "2018-01-01";
            cmd.Parameters.Add(date);
            using (IDataReader dr = cmd.ExecuteReader())
            {
                /****************
                 Logic to work on data 
                 received from SP
                *****************/
            }
        }
    }
}

When control comes to cmd.ExecuteReader(), it's failing with error:
Snowflake.Data: SQL compilation error: syntax error line 1 at position 0 unexpected 'SP_Snowflake_Procedure'.

I don't understand this Snowflake, how to call a procedure. I had a thought of, it is way similar to MS SQL. But I am wrong. I couldn't even find proper related documents.
I could use same code without procedure call but simple SELECT statement and worked fine.
Suggest me any changes here.

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-03-11T15:03:34

I can't tell from the code if you're using the ODBC driver for Snowflake or the .NET driver for Snowflake. The ODBC driver supports more features than the .NET driver, but I think executing SPs should be supported in both.

You'll need to make the call using a SQL statement that executes a query (as opposed to methods that execute non-query). It will return a table with a single row with the return from the SP. It will contain a single column with the name of the SP and the scalar value of the SP (basically what would be returned to the SQL worksheet if run in the web UI).

Here's a sample SP to test in case you need a simple one:

create or replace procedure EchoString(stringValue string)
returns VARCHAR
language JavaScript
as
  $$  

  // Note that variables passed to Snowflake stored procedures
  // muat be all CAPITAL letters when used in the body of the 
  // procedure code. 
  return STRINGVALUE

  $$;

--Run the stored procedure to echo the value.
call EchoString('Echo this string.');

Here's how to call the SP from a C# project using an ODBC connection:

OdbcConnection DbConnection = new OdbcConnection("DSN=Snowflake;pwd=******");
OdbcCommand DbCommandSetup = DbConnection.CreateCommand();
DbConnection.Open();

// These two lines are only required if you get a message about no running warehouse.
// It will depend on how your calling user is set up in Snowflake.
DbCommandSetup.CommandText = "use warehouse TEST;";
DbCommandSetup.ExecuteNonQuery();

OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "call TEST.PUBLIC.ECHOSTRING('Echo this string.')";
OdbcDataReader DbReader = DbCommand.ExecuteReader();

// Note: If you define a Snowflake SP, DB, or schema in mixed case without double quoting
// the name, Snowflake will uppercase it in the catalog. You can call it from here without
// converting to upper case as long as it's not double quoted (escaped \") in the string.

Call Snowflake Procedure from Azure Function App

1 Answers