DECLARE

Translation reference to convert SQL Server DECLARE statement to Snowflake Scripting

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 SQL Server 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.

SQL Server

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

Snowflake Scripting

CREATE OR REPLACE PROCEDURE BASICPROCEDURE ()
returns varchar
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    find varchar(30);
    find2 varchar(30) := 'Default';
    var varchar(5);
    var2 varchar(5);
BEGIN
    return '';
END;
$$;

Declare table variables

SQL Server 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.

SQL Server

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

Snowflake Scripting

CREATE OR REPLACE PROCEDURE BASICPROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
   CREATE TABLE MyTableVar(COLUMN1 VARCHAR(10));
   ...
   DROP TABLE MyTableVar;
   RETURN '';
END;
$$;

DECLARE statement outside routines (functions and procedures)

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

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 DECLARE statements, the BEGIN...END block should have a RETURN NULL statement to avoid errors, since this block can't be empty.

SQL Server

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

Snowflake Scripting

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.