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

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

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();

CREATE TABLE vEmployee (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

CREATE OR REPLACE PROCEDURE SetCursor()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    
DECLARE  
    CursorVar CURSOR(p) FOR 
        SELECT LastName, FirstName 
        FROM vEmployee  
        WHERE LastName like 'B%';  
BEGIN
    
    RETURN 0;
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.

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