Stored procedure not working while calling in snowflake

kathija 0
Viewed 30

Hope everyone is doing good.

I have started working on snowflake. I am trying to create a stored and procedure and calling this SP. Stored procedure is created without any issue. While calling it is saying that unexpected invalid token.

JavaScript compilation error: Uncaught Syntax issue: Invalid or unexpected token in USP_USERS at INSERT INTO stg.users_Temp position 0

enter image description here

CREATE OR REPLACE PROCEDURE ods.usp_Users()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_command = 
"INSERT INTO stg.users_Temp
    SELECT 
        LTRIM(RTRIM(id)) AS Id
    ,   LTRIM(RTRIM(name)) AS Name
    ,   LTRIM(RTRIM(divisionId)) AS DivisionId
    ,   LTRIM(RTRIM(divisionName)) AS DivisionName
    ,   LTRIM(RTRIM(department)) AS Department
    ,   LTRIM(RTRIM(email)) AS Email
    ,   LTRIM(RTRIM(state)) AS State
    ,   LTRIM(RTRIM(title)) AS Title
    ,   LTRIM(RTRIM(username)) AS Username
    ,   LTRIM(RTRIM(managerId)) AS ManagerId
    ,   LTRIM(RTRIM(employeeId)) AS employeeId
    ,   LTRIM(RTRIM(employeeType)) AS employeeType
    ,   LTRIM(RTRIM(officialName)) AS officialName
    ,   LTRIM(RTRIM(dateHire)) AS dateHire
    ,   LTRIM(RTRIM(LocationId)) AS LocationId
FROM stg.users;

    MERGE INTO 
        ods.Users  AS t 
    USING 
        stg.users_Temp s
    ON 
        (
            s.Id =  t.Id 
        )
    WHEN 
        MATCHED 
    THEN 
        UPDATE 
    SET  
         t.Name                 =   s.Name
        ,t.DivisionId               =   s.DivisionId
        ,t.DivisionName         =   s.DivisionName
        ,t.Department               =   s.Department
        ,t.Email                    =   s.Email
        ,t.State                    =   s.State
        ,t.Title                    =   s.Title
        ,t.Username             =   s.Username
        ,t.LocationId             =   s.LocationId
        ,t.ManagerId                =   s.ManagerId
        ,t.employeeId               =   s.employeeId
        ,t.employeeType         =   s.employeeType
        ,t.officialName         =   s.officialName
        ,t.dateHire             =   s.dateHire
        ,t.EtlLastUpdatedDate       =   CURRENT_TIMESTAMP() :: TIMESTAMP
    WHEN NOT MATCHED 
    THEN INSERT 
        (
             id
            ,Name
            ,DivisionId
            ,DivisionName
            ,Department
            ,Email
            ,State
            ,Title
            ,Username
            ,LocationId
            ,ManagerId
            ,employeeId     
            ,employeeType   
            ,officialName   
            ,dateHire   
            ,CurrentRecord
            ,EtlCreatedDate
        )
    VALUES 
        (
             s.id
            ,s.Name
            ,s.DivisionId
            ,s.DivisionName
            ,s.Department
            ,s.Email
            ,s.State
            ,s.Title
            ,s.Username
            ,s.LocationId
            ,s.ManagerId
            ,s.employeeId       
            ,s.employeeType 
            ,s.officialName 
            ,s.dateHire
            ,'1'
            ,CURRENT_TIMESTAMP() :: TIMESTAMP
        );"
 try { 
    snowflake.execute (
      {sqlText: sql_command}
      );
    return "Succeeded.";  // Return a success/error indicator.
    }
  catch (err) {
    return "Failed: " + err;  // Return a success/error indicator.
    }
TRUNCATE TABLE stg.users_Temp;
$$
;


//call ods.usp_Users();

While call SP it is throwing an error

Could someone help me on this issue.

Regards, Khatija

snowflake-cloud-data-platform
1 Answers

You need to use the backtick (`) character to define multi-line strings:

CREATE OR REPLACE PROCEDURE ods.usp_Users()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_command = 
`BEGIN
  INSERT INTO stg.users_Temp
    SELECT 
        LTRIM(RTRIM(id)) AS Id
    ,   LTRIM(RTRIM(name)) AS Name
    ,   LTRIM(RTRIM(divisionId)) AS DivisionId
    ,   LTRIM(RTRIM(divisionName)) AS DivisionName
    ,   LTRIM(RTRIM(department)) AS Department
    ,   LTRIM(RTRIM(email)) AS Email
    ,   LTRIM(RTRIM(state)) AS State
    ,   LTRIM(RTRIM(title)) AS Title
    ,   LTRIM(RTRIM(username)) AS Username
    ,   LTRIM(RTRIM(managerId)) AS ManagerId
    ,   LTRIM(RTRIM(employeeId)) AS employeeId
    ,   LTRIM(RTRIM(employeeType)) AS employeeType
    ,   LTRIM(RTRIM(officialName)) AS officialName
    ,   LTRIM(RTRIM(dateHire)) AS dateHire
    ,   LTRIM(RTRIM(LocationId)) AS LocationId
FROM stg.users;

    MERGE INTO 
        ods.Users  AS t 
    USING 
        stg.users_Temp s
    ON 
        (
            s.Id =  t.Id 
        )
    WHEN 
        MATCHED 
    THEN 
        UPDATE 
    SET  
         t.Name                 =   s.Name
        ,t.DivisionId               =   s.DivisionId
        ,t.DivisionName         =   s.DivisionName
        ,t.Department               =   s.Department
        ,t.Email                    =   s.Email
        ,t.State                    =   s.State
        ,t.Title                    =   s.Title
        ,t.Username             =   s.Username
        ,t.LocationId             =   s.LocationId
        ,t.ManagerId                =   s.ManagerId
        ,t.employeeId               =   s.employeeId
        ,t.employeeType         =   s.employeeType
        ,t.officialName         =   s.officialName
        ,t.dateHire             =   s.dateHire
        ,t.EtlLastUpdatedDate       =   CURRENT_TIMESTAMP() :: TIMESTAMP
    WHEN NOT MATCHED 
    THEN INSERT 
        (
             id
            ,Name
            ,DivisionId
            ,DivisionName
            ,Department
            ,Email
            ,State
            ,Title
            ,Username
            ,LocationId
            ,ManagerId
            ,employeeId     
            ,employeeType   
            ,officialName   
            ,dateHire   
            ,CurrentRecord
            ,EtlCreatedDate
        )
    VALUES 
        (
             s.id
            ,s.Name
            ,s.DivisionId
            ,s.DivisionName
            ,s.Department
            ,s.Email
            ,s.State
            ,s.Title
            ,s.Username
            ,s.LocationId
            ,s.ManagerId
            ,s.employeeId       
            ,s.employeeType 
            ,s.officialName 
            ,s.dateHire
            ,'1'
            ,CURRENT_TIMESTAMP() :: TIMESTAMP
        );
END`
 try { 
    snowflake.execute (
      {sqlText: sql_command}
      );
    return "Succeeded.";  // Return a success/error indicator.
    }
  catch (err) {
    return "Failed: " + err;  // Return a success/error indicator.
    }
// TRUNCATE TABLE stg.users_Temp;
$$
;

PS: You can't user trancate as you tried to do, you should also call it using snowflake.execute. And when you run this proc, it would fail with "Failed: Multiple SQL statements in a single API call are not supported; use one API call per statement instead.". To overcome this issue, you can break up queries, and execute them separately or surround them in a BEGIN/END block.

Gokhan Atil0
Related