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
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
BEGIN
DECLARE
T_MYTABLEVAR TABLE(
column1 VARCHAR(10));
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.
Last updated