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 .
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;
IN -> SqlServer_02.sql
CREATE TABLE vEmployee (
PersonID int,
LastName varchar(255),
FirstName varchar(255)
);
CREATE OR ALTER PROCEDURE SetCursor
AS
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 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;
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 VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0037 - SNOWFLAKE SCRIPTING CURSORS ARE NON-SCROLLABLE, ONLY FETCH NEXT IS SUPPORTED ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-TS0038 - 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)
SQL Server
IN -> SqlServer_03.sql
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
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 data type with 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.
// TSQL custom data type with mutator method
SET @p.SetXY(22, 23);
Related EWIs
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 .
