Translation reference to convert SQL Server SET statement to Snowflake
Description
Sets the specified local variable, previously created by using the DECLARE @local_variable statement, to the specified value. For more information for SQL Server SET, check here.
CREATE OR ALTER PROCEDURE SetProcedureAS DECLARE @MyCounter INT; DECLARE @FloatCounter FLOAT; --Numerical operators SET @MyCounter =3; SET @MyCounter +=1; --@MyCounter has 4 SET @MyCounter -=1; --@MyCounter has 3 SET @MyCounter *=2; --@MyCounter has 6 SET @MyCounter /=3; --@MyCounter has 2 SET @MyCounter =6; SET @MyCounter /=5; --@MyCounter has 1 SET @MyCounter =6; SET @MyCounter /=7; --@MyCounter has 0 SET @FloatCounter =10; SET @FloatCounter /=4; --@FloatCounter has 2.5 SET @MyCounter =6; SET @MyCounter %=4; --@MyCounter has 2--Logical operators SET @MyCounter &=3; --@MyCounter has 2 SET @MyCounter ^=2; --@MyCounter has 0 SET @MyCounter |=0; --@MyCounter has 0 RETURN @MyCounter;GODECLARE @result INT;EXEC @result = SetProcedure;PRINT @result;
IN -> SqlServer_02.sql
CREATE TABLE vEmployee ( PersonID int, LastName varchar(255), FirstName varchar(255));CREATE OR ALTER PROCEDURE SetCursorAS DECLARE @CursorVar CURSOR; SET @CursorVar = CURSOR SCROLL DYNAMIC FOR SELECT LastName, FirstName FROM vEmployee WHERE LastName like'B%'; GO
|Result |
|---------|
|0 |
Snowflake Scripting
OUT -> SqlServer_01.sql
CREATE OR REPLACE PROCEDURE SetProcedure ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTE AS CALLERAS$$ DECLARE MYCOUNTER INT; FLOATCOUNTER FLOAT; BEGIN--Numerical operators MYCOUNTER :=3; MYCOUNTER := MYCOUNTER +1; --@MyCounter has 4 MYCOUNTER := MYCOUNTER -1; --@MyCounter has 3 MYCOUNTER := MYCOUNTER *2; --@MyCounter has 6 MYCOUNTER := TRUNC(MYCOUNTER /3); --@MyCounter has 2 MYCOUNTER :=6; MYCOUNTER := TRUNC(MYCOUNTER /5); --@MyCounter has 1 MYCOUNTER :=6; MYCOUNTER := TRUNC(MYCOUNTER /7); --@MyCounter has 0 FLOATCOUNTER :=10; FLOATCOUNTER := FLOATCOUNTER /4; --@FloatCounter has 2.5 MYCOUNTER :=6; MYCOUNTER := MYCOUNTER % 4; --@MyCounter has 2--Logical operators MYCOUNTER := BITAND(MYCOUNTER, 3); --@MyCounter has 2 MYCOUNTER := BITXOR(MYCOUNTER, 2); --@MyCounter has 0 MYCOUNTER := BITOR(MYCOUNTER, 0); --@MyCounter has 0 RETURN :MYCOUNTER; END;$$;DECLARE RESULT INT;BEGIN CALL SetProcedure(); !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Print' NODE ***/!!! PRINT @result;END;
OUT -> SqlServer_02.sql
CREATE OR REPLACE TABLE vEmployee ( PersonID INT, LastName VARCHAR(255), FirstName VARCHAR(255))COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}';CREATE OR REPLACE PROCEDURE SetCursor ()RETURNS VARCHARLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'EXECUTE AS CALLERAS$$ DECLARE !!!RESOLVE EWI!!! /*** SSC-EWI-TS0037 - SNOWFLAKE SCRIPTING CURSORS ARE NON-SCROLLABLE, ONLY FETCH NEXT IS SUPPORTED ***/!!!--** SSC-FDM-TS0013 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ** CURSORVAR CURSOR FOR SELECT LastName, FirstName FROM vEmployee WHERE LastName like'B%'; BEGIN RETURN ''; END;$$;
|Result |
|---------|
|0 |
SET statement outside routines (functions and procedures)
Unlike SQL Server, Snowflake does not support executing isolated statements like SET outside routines like functions or procedures. For this scenario, the statement should be encapsulated in an anonymous block, as shown in the following examples. This statement is usually used after a DECLARE STATEMENT.
If there is a scenario with only SET statements, the DECLARE block is not necessary. Probably this scenario will produce runtime errors if there is an attempt of setting a value to a variable that is not declared.
SQL Server
IN -> SqlServer_04.sql
SET @Group =N'North America';
Snowflake Scripting
OUT -> SqlServer_04.sql
BEGIN_GROUP :='North America';END;
Known Issues
1. SET of a local variable with property name
This type of set is not currently supported by Snowflake scripting.
// TSQL custom datatypewith properties example DECLARE @p Point; SET @p.X = @p.X +1.1;
2. SET of a local variable with mutator method
This type of set is not currently supported by Snowflake scripting.