SET

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.

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 ] ] } ]   
      }   
    }  
}

Sample Source Patterns

SQL Server

Case 1

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;

Case 2

Result 1

Snowflake Scripting

Case 1

CREATE OR REPLACE PROCEDURE SetProcedure()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    BEGIN
        LET MyCounter INTEGER;  
        LET FloatCounter FLOAT;    
        
        --Numerical operators
        MyCounter := 3;
        MyCounter := MyCounter + 1; --@MyCounter has 4
        MyCounter := MyCounter - 1; --@MyCounter has 3
        MyCounter := MyCounter * 2; --@MyCounter has 6
              
        MyCounter := MyCounter / 3; --@MyCounter has 2
        MyCounter := 6;  
	MyCounter := TRUNC(MyCounter / 4);  --@MyCounter has 1
        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;
$$;

CALL SetProcedure();    

Case 2

Result 1

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.

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;

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

1. MSCEWI1058: FUNCTIONALITY FOR 'SET PROPERTY AND SET MUTATOR' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING.