DECLARE

Translation reference to convert Transact-SQL DECLARE statement to Snowflake Scripting

Applies to

• SQL Server

• Azure Synapse Analytics

Description

Transact-SQL DECLARE statement allows the creation of variables that can be used in the scope of the batch or a stored procedure. For more information regarding Transact-SQL DECLARE, check here.

-- Syntax for SQL Server and Azure SQL Database  
  
DECLARE   
{   
    { @local_variable [AS] data_type  [ = value ] }  
  | { @cursor_variable_name CURSOR }  
} [,...n]   
| { @table_variable_name [AS] <table_type_definition> }   
  
<table_type_definition> ::=   
     TABLE ( { <column_definition> | <table_constraint> } [ ,...n] )   
  
<column_definition> ::=   
     column_name { scalar_data_type | AS computed_column_expression }  
     [ COLLATE collation_name ]   
     [ [ DEFAULT constant_expression ] | IDENTITY [ (seed ,increment ) ] ]   
     [ ROWGUIDCOL ]   
     [ <column_constraint> ]   
  
<column_constraint> ::=   
     { [ NULL | NOT NULL ]   
     | [ PRIMARY KEY | UNIQUE ]   
     | CHECK ( logical_expression )   
     | WITH ( <index_option > )  
     }   
  
<table_constraint> ::=   
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n] )   
     | CHECK ( search_condition )   
     }

Sample Source Patterns

Declare variables

Variables can be created in different ways. Variables may or may not have a default value and several variables can be declared in the same line.

Notice that Snowflake Scripting does not allow to create more than one variable per line.

Transact-SQL

IN -> SqlServer_01.sql

DECLARE @find VARCHAR(30);
DECLARE @find2 VARCHAR(30) = 'Default';
DECLARE @var VARCHAR(5), @var2 varchar(5);

Snowflake Scripting

OUT -> SqlServer_01.sql

DECLARE
FIND VARCHAR(30);
FIND2 VARCHAR(30) := 'Default';
VAR VARCHAR(5);
VAR2 VARCHAR(5);
BEGIN
RETURN '';
END;

Declare table variables

Transact-SQL allows the creation of table variables that can be used as regular tables. Snowflake scripting does not support this, instead, a table can be created and then dropped at the end of the procedure.

Transact-SQL

IN -> SqlServer_02.sql

DECLARE @MyTableVar TABLE(  
    column1 varchar(10));

Snowflake Scripting

OUT -> SqlServer_02.sql

BEGIN
    DECLARE
        T_MYTABLEVAR TABLE(
            column1 VARCHAR(10));
END;

DECLARE statement outside routines (functions and procedures)

Unlike Transact-SQL, Snowflake does not support executing isolated statements like DECLARE 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 before a SET 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 DECLARE statements, the BEGIN...END block should have a RETURN NULL statement to avoid errors, since this block can't be empty.

Transact-SQL

IN -> SqlServer_04.sql

DECLARE @Group nvarchar(50), @Sales MONEY;

Snowflake Scripting

OUT -> SqlServer_04.sql

DECLARE
_GROUP VARCHAR(50);
SALES NUMBER(38, 4);
BEGIN
RETURN '';
END;

Known Issues

No issues were found.

Related EWIs

No related EWIs.