DECLARE PARAM

The DECLARE / DECLARE PARAM command defines a script variable that can parameterize most of the SQL CDC script. Parameter values can be overridden by a calling script when using the preview operation, but cannot be overridden that way when the pipeline runs as a scheduled operation. You can optionally specify default values for parameters; while optional, this is recommended in case no values are provided when the pipeline runs.

Script variables declared with DECLARE can be modified during script execution with SET. If the declared variable name matches an environment variable of the same name (for example when initialized with #getvar() / #trygetvar()), SET updates both the script variable and the environment variable. In other words, script variables and environment variables may be linked using this approach.

Both default values and overridden values support the use of DataZen functions.

To pass values to the /sql/preview endpoint, you must provide a JSON Array of objects in an HTML comment section (see example below).

See also Job Runtime Variables and Environment Variables.

Declares a parameter that can be overriden when starting a SQL CDC script by including a JSON-formatted parameter block.

DECLARE { PARAM }
	@variable { = < '...' | N | true | false > }
;

PARAM	The name of the parameter being defined
VALUE	The default value to use for this parameter; may be overriden by a parameter block injected at runtime in the SQL CDC script

Example 1

-- Declare a parameter to be used in the SQL CDC script and set a default value
-- The parameter can be overriden, but it is read-only once the script starts
DECLARE @url = 'econi.xml';

-- Parameters can be used throughout the script using the @param notation
SELECT * FROM HTTP [Rss Feed] (GET /{{@url}}) APPLY TX (//item);

Example 2

/*

Use the parameter value declaration section as shown below to override 
the SQL CDC parameters declared. 

<!--param
	[ { "url": "econi.xml" } ]
-->

*/

-- Declare a parameter to be used in the SQL CDC script and set a default value
-- The parameter can be overriden, but it is read-only once the script starts
DECLARE @url = 'TEST';

-- Parameters can be used throughout the script using the @param notation
SELECT * FROM HTTP [Rss Feed] (GET /{{@url}}) APPLY TX (//item);