October 21, 2022

How to Translate Stored Procedures to Snowflake Scripting Using phData SQLMorph

By Anish Kumar

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:

  1. The application can perform complex transformations as a true transpiler or source-to-source compiler that many regular-expression-based tools cannot.
  2. As a SaaS service, the application is available to anyone who might need it instead of only a few people who have it installed.
  3. Translate as many expressions as possible from the source to the target.
  4. 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.
The supported source SQL dialects in SQLMorph
The supported source SQL dialects in SQLMorph

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.
phdata-tool-kit
  • Go to the Settings page by clicking on the Settings button.
tool-kit-settings
  • Choose Snowflake Scripting as the Procedure type (as highlighted below). By default, the procedure type is Javascript.
tool-kit-square
  • After the above setting, move to the Translate tab and select Microsoft SQL Server from the source dialect dropdown if the source dialect differs.
select Microsoft SQL Server
  • 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
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.

SourceTarget
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

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit