The ISEMPTY() built-in functions returns true if the SQL iQuery Script Session Variable exists and it has no value assigned to it (it is empty), otherwise it returns false.
The ISNOTEMPTY() built-in function returns true if the SQL iQuery Script Session Variable exist and has a value assigned to it. If the variable is not defined or is defined but has no value assigned to it, it return false.
The ISNOTEMTPY() built-in function can be used to determine if a Session Variable Exists and contains data. Use this when writing dynamic WHERE clause on your SQL SELECT statement--omitting conditions when the user did not specify a value.
The ISEMPTY() built-in function can be used to determine what to do when a Session Variable Exists and is empty or does not exist. For example, you may wish to assign a default value to the variable if it is empty.
#default &Company = 01
#define ® = ''
#h2 Region &Reg
eval &Company = 99;
select * from foo F
WHERE F.region = '®'
The two Session Variables &COMPANY and ® are declared. &COMPANY is initialized to 01, while ® is empty. The IF ISNOTEMPTY(®) returns false since although ® is defined, it contains no value. The IF ISEMPTY(&COMPANY) will return false because &COMPANY was initialized to 01.