CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platform
PIG
PIG

Reputation: 602

100132 (P0000): JavaScript execution error: Uncaught ReferenceError:

I am practicing use cases of try and catch for stored procedure Getting error num is not defined. But getting below complete error.

100132 (P0000): JavaScript execution error: Uncaught ReferenceError: num is not defined in SP at '    var sql_comm = "insert into test_sp (col) values(" + num + ")";' position 57

stackstrace: SP line: 2

Below is my code

    create or replace TABLE TEST_SP (
       COL NUMBER(38,0)                                                                       
 );

create or replace procedure sp ( num float )
    returns float
    language javascript
    strict
    execute as owner
    as
    $$
    var sql_comm = "insert into test_sp values(" + num + ")";
    try {
            snowflake.execute(
                    {sqlText:sql_comm}
                );
            return "succeeded"
    }
    catch (err){
        return "failed" + err ;
    }
    $$
    ;

    call sp(2::float);

Upvotes: 2

Views: 6052

Answers (2)

Sriga
Sriga

Reputation: 1321

All the parameters inside stored procedure should be in upper case, Change the parameter to upper case and it will resolve the issue.

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

The parameter NUM inside the stored procedure needs to be in all uppercase. And you are returning float but returning strings/text after that is fixed, so in my example I changed to return TEXT, but I suspect that was just a function of your testing.

create or replace procedure sp ( num float )
returns text
language javascript
strict
execute as owner
as
$$
var sql_comm = "insert into test_sp values(" + NUM + ")";
try {
        snowflake.execute(
                {sqlText:sql_comm}
            );
        return "succeeded"
}
catch (err){
    return "failed" + err ;
}
$$
;

works:

call sp(2::float);

giving:

SP
succeeded

and checking:

select * from test_sp;

gives:

COL
2
2
2

Upvotes: 1

Related Questions