Call stored procedure in Snowflake query

November 28, 2022

I have a stored procedure, called SP_CALC_BUCKET, defined. I can call it like so:

CALL SP_CALC_BUCKET('1972-10-01', 2, 6)

and this works, and returns the result I expect. However, if I try to call this stored procedure for each row returned from a SELECT statement:


SELECT SP_CALC_BUCKET($1, $2, $3) 
FROM (VALUES ('2022-01-01', 5, 4), ('1972-10-01', 1, 6), ('2008-08-08', 1, 7), 
    ('1999-12-31', 2, 8), ('2000-01-01', 0, 10))

I get the following error:

Unknown user-defined function SP_CALC_BUCKET

Clearly, the Syntax is informing Snowflake that it should look for a UDF called SP_CALC_BUCKET. So, then, how do I modify this query so that it works?

>Solution :

You cannot use a stored proc in SQL. You should register a function and use it.

Please refer the following from doc:

Stored Procedures Are Called as Independent Statements
A function evaluates to a value, and can be used in contexts in which a general expression can be used (e.g. SELECT my_function() …).

A stored procedure does not evaluate to a value, and cannot be used in all contexts in which a general expression can be used. For example, you cannot execute SELECT my_stored_procedure()….

A stored procedure is called as an independent statement. The code below illustrates the difference between calling a stored procedure and calling a function:

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;