How to Translate Stored Procedures to Snowflake Scripting Using phData SQLMorph

In this blog, we’ll explore phData’s SQL Translation App (SQLMorph) and how to use it for translating stored procedures to Snowflake Scripting from the Snowflake Data Cloud.

What is the SQL Translation App?

The SQL Translation App (aka SQLMorph) in the phData Toolkit is a SaaS application that instantly translates SQL from one SQL dialect to another. It’s like Google Translate — but for SQL dialects. It enables you to quickly evaluate and migrate to new databases, save time and costs by eliminating manual work, and reduce errors.

Below are some features of SQLMorph:

The application can perform complex transformations as a true transpiler or source-to-source compiler that many regular-expression-based tools cannot.
As a SaaS service, the application is available to anyone who might need it instead of only a few people who have it installed.
Translate as many expressions as possible from the source to the target.
When there are issues or notable changes during translation, clear messages and status are provided back to the user to ensure no invalid translation is used and the following steps to fixing the query are clear.

Many customers are moving to the Snowflake Data Cloud and SQLMorph is heavily focused on Snowflake as the target SQL dialect. In addition to plain SQL query translation, the tool supports the translation of Oracle and Microsoft SQL Server (MSSQL) stored procedures to Snowflake stored procedures in Javascript. SQLMorph will continues to evolve as Snowflake releases new functionalities.

What’s New to the SQL Translation App

With the addition of Snowflake Scripting, it now makes it easier for users to write stored procedures using Snowflake SQL. We recently enhanced the phData SQL Translation app to include this feature when translating the MSSQL dialect. The other dialects will get this support soon.

This article will mainly focus on translating the MSSQL stored procedures to Snowflake Scripting.

How to Translate MSSQL Stored Procedure to Snowflake Scripting

To begin, follow the steps below to translate the stored procedure to Snowflake Scripting:

Login to the phDataToolkit.
Click on either of the SQL Translation apps as highlighted below.

Go to the Settings page by clicking on the Settings button.

Choose Snowflake Scripting as the Procedure type (as highlighted below). By default, the procedure type is Javascript.

After the above setting, move to the Translate tab and select Microsoft SQL Server from the source dialect dropdown if the source dialect differs.

Now, we are ready to translate the query. Put the stored procedure on the left side of the screen and click on the Translate button. The right side of the screen shows the translated Snowflake Scripting

Snowflake Scripting Translation Examples

Below are some examples to highlight the functionality of the automated translation. We will discuss some examples below to show the translations covered in the tool so far. The query in the Source column is the input MSSQL stored procedure, and the statement in the Target column is the automatically generated Snowflake Scripting translation.

Translating Declare Variables Statement.

Source

Target

create procedure proc_variables
as
Declare @v1 nvarchar(4), @v2 nvarchar(10), @v3 numeric, @v4 nvarchar(15), @v5 numeric, @v6 nvarchar(20);
Set @v1 = ‘a’
Set @v2 = ‘b’
Set @v3 = 10;
Set @v4 = @v1 + ‘c’;
Set @v5 = @v3 + 10;
Set @v6 = @v1 + ‘d’ + @v2 + @v4 ;
Select @v5, @v6;

CREATE OR REPLACE PROCEDURE proc_variables ()
RETURNS STRING
LANGUAGE SQL as $$
BEGIN
LET v1 VARCHAR(4);
LET v2 VARCHAR(10);
LET v3 NUMERIC;
LET v4 VARCHAR(15);
LET v5 NUMERIC;
LET v6 VARCHAR(20);
v1 := ‘a’;
v2 := ‘b’;
v3 := 10;
v4 := :v1 || ‘c’;
v5 := :v3 + 10;
v6 := :v1 || ‘d’ || :v2 || :v4;
SELECT :v5, :v6;
END;
$$;

This example also shows how effectively the app can replace MSSQL “+” operator with the “||” operator in snowflake scripting for string concatenation.

Translating Declare Cursor, Open Cursor, Fetch Cursor, and Close Cursor Statements.

Source

Target

create procedure proc_cursor
as
DECLARE @LastName nvarchar(34);
DECLARE vend_cursor CURSOR FOR SELECT lastName FROM Purchasing.Vendor;
OPEN vend_cursor;
FETCH NEXT FROM vend_cursor INTO @LastName;
CLOSE vend_cursor;

CREATE OR REPLACE PROCEDURE proc_cursor ()
RETURNS STRING
LANGUAGE SQL as $$
BEGIN
LET LastName VARCHAR(34);
LET vend_cursor CURSOR FOR
SELECT lastName FROM Purchasing.Vendor;
OPEN vend_cursor;
— WARNING: line 6, character 6: Equivalent of argument ‘NEXT’ not found in Snowflake FETCH statement.
FETCH vend_cursor INTO :LastName;
CLOSE vend_cursor;
END;
$$;

Note: We are providing a warning message so that users can validate the output based on their use case. In some cases, the MSSQL fetch cursor does not support INTO clause whereas Snowflake expects the INTO clause. In such cases, we would provide an error message.

Translating IF…ELSE Statement.

Source	Target
*CREATE PROCEDURE proc_if_stmt* AS IF 5>4 SELECT *‘Weekend’; ELSE SELECT* ‘Weekday’**;	*CREATE OR REPLACE PROCEDURE proc_if_stmt ()* *RETURNS STRING* *LANGUAGE SQL as $$* *BEGIN* *IF (5 > 4) THEN SELECT* *‘Weekend’; ELSE* *SELECT* *‘Weekday’; END* *IF;* *END;* *$$;*

Translating WHILE Statement.

Source	Target
*CREATE PROCEDURE proc_while_stmt AS* *DECLARE @caseVal* *bigint* *= case* *when* 1 = 1 then 3 end; *WHILE (SELECT AVG(lprice) FROM t1) <* *300* *BEGIN* UPDATE t1 SET lprice = lprice * 2; *Select MAX(lprice) FROM t1;* *BREAK;* *END;*	*CREATE OR REPLACE PROCEDURE proc_while_stmt ()* *RETURNS STRING* *LANGUAGE SQL as $$* *BEGIN* *LET caseVal* *BIGINT* *DEFAULT CASE* *WHEN* 1 = 1 THEN 3 *END;* *WHILE ( ( SELECT AVG(lprice) FROM t1 ) <* *300) DO*** UPDATE t1 SET lprice = lprice * 2; *SELECT MAX(lprice) FROM t1;* *break;* *END* *WHILE;* *END;* *$$;*

Translating CASE Statement.

Source	Target
*CREATE PROCEDURE proc_case_stmt @vin* *bigint* AS *declare @v1* *bigint* *= (select max(id) from t1);* *declare @v2* *bigint* *= case when @v1 < @vin then @v1 else @vin end;*	*CREATE* OR *REPLACE* *PROCEDURE* proc_case_stmt (vin FLOAT) *RETURNS* *STRING* *LANGUAGE* *SQL* as $$ *BEGIN* LET v1 BIGINT *DEFAULT* ( *SELECT* *MAX(id) FROM* t1 ); LET v2 BIGINT DEFAULT CASE WHEN :v1 < :vin THEN :v1 ELSE :vin *END; END; $$;*

Translating Exception Handling TRY…CATCH Statement.

Source	Target
*create procedure proc_try_catch* as *BEGIN TRY* *SELECT* 1/0; *END TRY* *BEGIN CATCH* *Select* 10/2; *END CATCH* ;	*CREATE OR REPLACE PROCEDURE proc_try_catch ()* *RETURNS STRING* *LANGUAGE SQL as $$* *BEGIN* *SELECT* 1 / 0; *EXCEPTION* *WHEN OTHER THEN* *SELECT* 10 / 2; *END;* *$$;*

Translate to Snowflake Anonymous Block.

Enclose MSSQL procedural code with BEGIN and END statements to convert it to Snowflake Scripting anonymous block.

Source	Target
BEGIN IF 5>4 SELECT ‘Weekend’; ELSE SELECT ‘Weekday’; END;	EXECUTE IMMEDIATE $$ BEGIN IF (5 > 4) THEN SELECT ‘Weekend’; ELSE SELECT ‘Weekday’; END IF; END; $$ ;

What’s Next

We highly recommend exploring SQL Translation tool available in the phData Toolkit. You can test out the demo environment without account creation.

In addition, we encourage you to try converting the MSSQL stored procedures to Snowflake Scripting. Be sure to reach out to the team to report bugs, ask questions, and provide feedback

How to Translate Stored Procedures to Snowflake Scripting Using phData SQLMorph

What is the SQL Translation App?

What’s New to the SQL Translation App

How to Translate MSSQL Stored Procedure to Snowflake Scripting

Snowflake Scripting Translation Examples

Translating Declare Variables Statement.

Translating Declare Cursor, Open Cursor, Fetch Cursor, and Close Cursor Statements.

Translating IF…ELSE Statement.

Translating WHILE Statement.

Translating CASE Statement.

Translating Exception Handling TRY…CATCH Statement.

Translate to Snowflake Anonymous Block.

More to explore

Join our team

Partners

Resources

Software

Accelerate and automate your data projects with the phData Toolkit