Lack of table output functionality in Snowflake Stored Procedures

Question

I am re-architecting our company's APIs to use Snowflake from SQL Server. Our first immediate roadblock is the fact that our existing SQL Server stored procedures return table output and Snowflake does not allow that type of output. I don't care to completely re-design everything so I am thinking:

1. Rebuild my Snowflake stored procedures the same way as SQL Server
2. Before exit, dump the output into a temp table.
3. My return value is the table name.
4. Now I can return the data from the temp table.

Does anyone else have any better suggestions for how to handle this?

I know for coding, it will be much easier to do this using something like python, however, I don't want to maintain another python application just for data retrieval within snowflake.

Answer 1

Snowflake documentation is not only describing technical details, but also usage patterns and this case is already described at:

Overview of Stored Procedures

there are indirect ways to use the return value of a stored procedure:

• You can call the stored procedure inside another stored procedure; the JavaScript in the outer stored procedure can retrieve and store the output of the inner stored procedure. Remember, however, that the outer stored procedure (and each inner stored procedure) is still unable to return more than one value to its caller.

• You can call the stored procedure and then call the RESULT_SCAN function and pass it the statement ID generated for the stored procedure.

• You can store a result set in a temporary table or permanent table, and use that table after returning from the stored procedure call.

• If the volume of data is not too large, you can store multiple rows and multiple columns in a VARIANT (for example, as a JSON value) and return that VARIANT.