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

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