DECLARE Variables (U-SQL)

The DECLARE statement initializes a U-SQL variable.

Variable :=                                                                                              
    System_Variable | User_Variable.

System_Variable := '@@'+Name.

User_Variable := "@"+Unquoted_Identifier.

Variables allow you to name scalar and rowset expressions. A simple example should illustrate how this works:

DECLARE @city string = "Seattle";  

You can do more than store simple values. You can use C# Expressions to compute values

DECLARE @city string = "Seat" + "tle";  

You can even call limited set of methods that can be constant-folded.

DECLARE @city string = ("Seat"+ "tle").ToUpper();  

Custom assembly objects, such as c# user-defined functions, can be referenced inside a DECLARE statement.

REFERENCE ASSEMBLY myassembly;

DECLARE @myvar string = "string "+myns.myclass.mystringfn();

DECLARE EXTERNAL
DECLARE EXTERNAL allows the declaration of a script scalar expression variable that can be overwritten by a previous DECLARE statement without failing compilation.

For example, the following script will produce the specified file with content "external declaration":

DECLARE EXTERNAL @value string = "external declaration";

@r = SELECT * FROM (VALUES(@value)) AS T(x);
OUTPUT @r TO "/output/test.csv" USING Outputters.Csv();

The following script on the other hand will produce the file with the content "overwritten declaration". This allows users to parameterize scripts with a default and allows tools (such as Azure Data Factory) to provide a parameter model and overwrite the default parameter values.

DECLARE @value string = "overwritten declaration";
DECLARE EXTERNAL @value string = "external declaration";

@r = SELECT * FROM (VALUES(@value)) AS T(x);
OUTPUT @r TO "/output/test.csv" USING Outputters.Csv();

Differences from T-SQL
Unlike T-SQL, a variable must be declared and initialized in the same statement. In addition, SET cannot be used to update user variables. You must DECLARE a new variable instead of using SET to update an existing one. For example:

// Will fail
DECLARE @var string;

// Will also fail
DECLARE @var string = "";
SET @var = "a value"

• Using Variables (U-SQL)