Here is the error message
JavaScript compilation error: Uncaught SyntaxError: Unexpected token '>' in HP_SEARCHCBHMESSAGES at ' if (Fac123 <> "") ' position 1..
Some reason SF doesn't like if condition (Fac123 <> "" ), have tried if (Fac123 <> '' ) but same error
Please help!
CALL PROC1('param1' )
CREATE OR REPLACE PROCEDURE PROC1("param1" nvarchar(100) )
returns varchar
language javascript
AS
$$
var Fac123 = param1
**if (Fac123 <> "" )**
{
sql12 = " AND "
}
return sql12 ;
Inside a JavaScript procedure the only valid language to use is Javascript.
Therefore you have to use a javascript "not equals" which is !==
CREATE OR REPLACE PROCEDURE PROC1("param1" nvarchar(100) )
returns varchar
language javascript
AS
$$
var Fac123 = param1
if (Fac123 !== "" )
{
sql12 = " AND "
}
return sql12 ;
$$;
now it can be used
call PROC1('blar');
gives:
| PROC1 |
|---|
| AND |
but passing in an empty string..
call PROC1('');
gives an error, because your code doesn't handle that path yet.
100132 (P0000): JavaScript execution error: Uncaught ReferenceError: sql12 is not defined in PROC1 at 'return sql12 ;' position 0
stackstrace:
PROC1 line: 8
The provided code suggests a trial to build a code generator, a pattern called kitchen sink(i.e. if a parameter is provided it should be included otherwise removed from WHERE condion).
Alternative approach is to simply use NULL safe comparison. If the missing value is passed as empty string then:
SELECT *
FROM ...
WHERE ...
AND col IS NOT DISTINCT FROM COALESCE(NULLIF("param1", ''), col)
If the missing value is passed as NULL from the app then it is much simpler:
SELECT *
FROM ...
WHERE ...
AND col IS NOT DISTINCT FROM COALESCE("param1", col)
This way all to stack the conditions in advance using AND:
SELECT *
FROM ...
WHERE ...
AND col IS NOT DISTINCT FROM COALESCE("param1", col)
AND col2 IS NOT DISTINCT FROM COALESCE("param2", col2)
AND col3 IS NOT DISTINCT FROM COALESCE("param3", col3)
-- ...
Params provided as NULL are defaulted to colX and colX <=> colX is always true.
Need to fix 'if' statement and put semi-colon after var declaration, please refer below -
Error Code -
CREATE OR REPLACE PROCEDURE PROC1("param1" nvarchar(100) )
returns varchar
language javascript
AS
$$
var Fac123 = param1
if (Fac123 <> "" )
{
sql12 = " AND "
}
return sql12 ;
$$
;
+--------------------------------------+
| status |
|--------------------------------------|
| Function PROC1 successfully created. |
+--------------------------------------+
1 Row(s) produced. Time Elapsed: 0.151s
CALL PROC1('param1' );
100131 (P0000): JavaScript compilation error: Uncaught SyntaxError: Unexpected token '>' in PROC1 at 'if (Fac123 <> "" )' position 12
Fixed code -
CREATE OR REPLACE PROCEDURE PROC1("param1" nvarchar(100) )
returns varchar
language javascript
AS
$$
var Fac123 = param1;
if (Fac123 != "" )
{
sql12 = " AND "
}
return sql12 ;
$$
;
+--------------------------------------+
| status |
|--------------------------------------|
| Function PROC1 successfully created. |
+--------------------------------------+
CALL PROC1('param1' );
+-------+
| PROC1 |
|-------|
| AND |
+-------+