SET
Translation reference to convert SQL Server SET statement to Snowflake
Last updated
Translation reference to convert SQL Server SET statement to Snowflake
Last updated
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.
There are four SET cases that are the following:
SET
{ @local_variable
[ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
|
{ @SQLCLR_local_variable.mutator_method
}
|
{ @local_variable
{+= | -= | *= | /= | %= | &= | ^= | |= } expression
}
|
{ @cursor_variable =
{ @cursor_variable | cursor_name
| { CURSOR [ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
}
}
}
CREATE OR ALTER PROCEDURE SetProcedure
AS
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;
GO
DECLARE @result INT;
EXEC @result = SetProcedure;
PRINT @result;
CREATE OR REPLACE PROCEDURE SetProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
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;
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.
SQL Server
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
Snowflake Scripting
DECLARE
_GROUP VARCHAR(50);
SALES NUMBER(38, 4);
BEGIN
_GROUP := 'North America';
SALES := 2000000;
END;
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
SET @Group = N'North America';
Snowflake Scripting
BEGIN
_GROUP := 'North America';
END;
This type of set is not currently supported by Snowflake scripting.
// TSQL custom data type with properties example
DECLARE @p Point;
SET @p.X = @p.X + 1.1;
This type of set is not currently supported by Snowflake scripting.
// TSQL custom data type with mutator method
SET @p.SetXY(22, 23);
SSC-EWI-TS0037: Snowflake Scripting Cursors are non-scrollable.
SSC-EWI-0073: Pending Functional Equivalence Review.