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

IN -> SqlServer_01.sql
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;

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;