SET

Translation reference to convert Transact-SQL SET statement to Snowflake

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

Description

Sets the specified local variable, previously created by using the DECLARE @local_variable statement, to the specified value. For more information for Transact-SQL 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

Transact-SQL

Case 1

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;

Case 2

Result 1

Snowflake Scripting

Case 1

OUT -> SqlServer_01.sql

CREATE OR REPLACE PROCEDURE SetProcedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
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 := MYCOUNTER / 3;  --@MyCounter has 2

        MYCOUNTER := 6;
        MYCOUNTER := MYCOUNTER / 5;  --@MyCounter has 1

        MYCOUNTER := 6;
        MYCOUNTER := 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;

Case 2

Result 1

SET statement outside routines (functions and procedures)

Unlike Transact-SQL, 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.

Transact-SQL

IN -> SqlServer_03.sql

DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;

Snowflake Scripting

OUT -> SqlServer_03.sql

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.

Transact-SQL

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

1. SSC-EWI-TS0037: Snowflake Scripting Cursors are non-scrollable.

2. SSC-EWI-0073: Pending Functional Equivalence Review.

3. SSC-FDM-TS0013: Snowflake Scripting cursor rows are not modifiable.