DECLARE Statement

Creates a new variable. Variables must be declared before use. Variables have script scope. That is, the variable can be used anywhere else in the script, but it will not be visible to other scripts called using EXECUTE, nor to the parent script if this script was itself called with EXECUTE. Variable names must begin with an at sign (@), dollar sign ($), or colon (:).

If the PARAMETER keyword is used, then the variable becomes a parameter to the script. If the script is called using EXECUTE, then the caller must provide a value for this parameter unless the DECLARE PARAMETER statement specifies an value.

If the DECLARE statement does not include the PARAMETER keyword and does not specify an value, then the variable takes the initial value of NULL.

Unlike other popular SQL scripting languages, SQL Notebook's DECLARE statement does not require a data type to be specified. Variables follow the SQLite convention of allowing any data type to be stored.

Syntax

Parameters

• PARAMETER (keyword)
  If specified, then the variable is a parameter to the script, for which callers using the EXECUTE statement can specify an argument value. If the value argument is specified, then the parameter is optional and may be omitted by EXECUTE callers. If no value is specified, then callers must provide an argument value for this parameter.
• name (identifier)
  A name beginning with an at sign (@), dollar sign ($), or colon (:). The name must not have been previously declared in this script. To change the value of an existing variable, use the SET statement.
• value: scalar
  If provided, the variable will be assigned this value. It may be a scalar expression or a parentheses-enclosed SELECT statement. If not provided, the variable will be assigned a value of NULL. The value can be changed after declaration using the SET statement.

Example

-- @a, @b, and @c are local variables.
DECLARE @a = 1 + 2;
PRINT @a;  -- "3"

DECLARE @b = (SELECT COUNT(*) FROM sqlite_master);
PRINT @b;  -- "0"

DECLARE @c;
IF @c IS NULL
    PRINT 'c is null';  -- Prints.

-- Creates a parameter variable called @requiredParam. Because
-- there is no initial value specified, the caller of this script
-- must provide a value for this parameter.
DECLARE PARAMETER @requiredParam;

-- Creates a parameter variable called @optionalParam. Because
-- an initial value of 5 is specified, the caller is not required
-- to provide a value for this parameter, but may do so if it wants
-- to override the default value.
DECLARE PARAMETER @optionalParam = 5;

See Also

• SET Statement
  
• EXECUTE Statement