When Migrating from SQL Server to Snowflake how do we migrate stored procedures

Question

I am doing POC on Snowflake and exploring this product.

I have a question: in SQL Server, we have 400-500 stored procedures and these stored procedures are getting called from SSRS reports. These stored procedures are simple in Nature like below one:

CREATE PROCEDURE [dbo].[SQL_Stored_Procedure]
    (@StartDate DATETIME, 
     @EndDate   DATETIME)
AS
BEGIN
    SELECT *
    FROM MYTable
    WHERE Cloumn_Date BETWEEN @StartDate AND @EndDate;
END

EXEC [dbo].[SQL_Stored_Procedure] @StartDate = Getdate()-1, @EndDate=Getdate()

How can I achieve same in Snowflake? Seems Snowflake procedure is like a SQL Server function: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html

Curious to know how other people are doing in above scenario..?

Answer 1

For some use cases UDTFs might be a better option, but you can use the javascript stored procedures to run SQL statements. It's not super pretty, and I'm hoping that They add SQL as a language option in stored procedures soon. Here's an example with your generic query above.

CREATE OR REPLACE PROCEDURE STORED_PROCEDURE_NAME(STARTDATE VARCHAR, ENDDATE VARCHAR)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS 
$$
    query = `SELECT column1, column2
            FROM MYTable
            WHERE Cloumn_Date BETWEEN '` + STARTDATE + `' AND '` + ENDDATE + `';`

    resultset = snowflake.createStatement({sqlText: query}).execute() 

    //extract the results of the query into a javascript object
    output = []
    while (resultset .next())  {
        output.push(output.getColumnValue(1), output.getColumnValue(2));
    }

    //put the output of the query into a JSON object which Snowflake can translate
    // into a "variant" and can be used downstream
    json = { "result" : output };

    return json;
$$;

//I've found it easiest to work with strings because the data type conversions
//between snowflake and javascript and back again can be tricky
CALL STORED_PROCEDURE_NAME(CURRENT_DATE::STRING, DATEADD( DAY, 1, CURRENT_DATE)::STRING);
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

I've found this to be mostly useful for procedures that execute transformations and loads, but so far I've only manually migrated a smallish set of queries and has required some javascript fiddling and customization for some of them.

This will be a little tricky to set up to automatically migrate hundreds of queries, but the javascript above can be improved and generalized even more (I'm not a javascript expert). For example, to dynamically handle any column list without manual editing: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#returning-a-result-set

Documentation for the extra RESULT_SCAN step for using the result downstream is here: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#using-result-scan-to-retrieve-the-result-from-a-stored-procedure