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.
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.
- 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.
create procedure proc_variables
CREATE OR REPLACE PROCEDURE proc_variables ()
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.
|create procedure proc_cursor |
DECLARE @LastName nvarchar(34);
DECLARE vend_cursor CURSOR FOR SELECT lastName FROM Purchasing.Vendor;
FETCH NEXT FROM vend_cursor INTO @LastName;
|CREATE OR REPLACE PROCEDURE proc_cursor ()|
LANGUAGE SQL as $$
LET LastName VARCHAR(34);
LET vend_cursor CURSOR FOR
SELECT lastName FROM Purchasing.Vendor;
— WARNING: line 6, character 6: Equivalent of argument ‘NEXT’ not found in Snowflake FETCH statement.
FETCH vend_cursor INTO :LastName;
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.
CREATE PROCEDURE proc_if_stmt
CREATE OR REPLACE PROCEDURE proc_if_stmt ()
Translating WHILE Statement.
CREATE PROCEDURE proc_while_stmt AS
CREATE OR REPLACE PROCEDURE proc_while_stmt ()
Translating CASE Statement.
CREATE PROCEDURE proc_case_stmt @vin bigint
CREATE OR REPLACE PROCEDURE proc_case_stmt (vin FLOAT)
Translating Exception Handling TRY…CATCH Statement.
create procedure proc_try_catch
CREATE OR REPLACE PROCEDURE proc_try_catch ()
Translate to Snowflake Anonymous Block.
Enclose MSSQL procedural code with BEGIN and END statements to convert it to Snowflake Scripting anonymous block.
EXECUTE IMMEDIATE $$
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