CODEWITHSUNDEEP
sqlstored-proceduressnowflake-cloud-data-platform
Ruwanika
Ruwanika

Reputation: 3

Syntax error: unexpected '='. when writing a Snowflake stored procedure

I am new to writing stored procedures in Snowflake. When I write the code for my stored procedure, I get an error

Syntax error: unexpected '='. (line 12)

This is my code:

CREATE OR REPLACE PROCEDURE my_proc()
RETURNS STRING
LANGUAGE SQL
AS 
$$

-- Declare variables
DECLARE 
        demo_latest TIMESTAMP_LTZ;
        demo_current TIMESTAMP_LTZ;
        demo_difference INT;
        demo_current_week DATE;

-- Set variables
BEGIN
    demo_latest = (SELECT MAX(begin_date_time) FROM demo_original_transactions_dateshift);
    demo_current = CURRENT_TIMESTAMP();
    demo_current_week = DATEADD(DAY, -DAYOFWEEKISO(CURRENT_DATE) + 1, CURRENT_DATE);
    demo_difference = DATEDIFF('DAY', demo_latest, demo_current_week); 
    

-- Update statements with date shifting

    --create or replace table demo_original_transactions_dateshift AS (select * from demo_original_transactions);

    UPDATE demo_original_transactions_dateshift
        SET begin_date_time = DATEADD(DAY, demo_difference, begin_date_time),
        end_date_time = DATEADD(DAY, demo_difference, end_date_time)

END;
$$
;

Upvotes: 0

Views: 91

Answers (2)

samhita
samhita

Reputation: 3505

As mentioned in the above comment by @NickW, you need to use := instead of = to assign a value to a variable.

I found there are other errors as well in your procedure

  1. You need to use () to call CURRENT_DATE like so CURRENT_DATE()

  2. demo_difference needs to be used as :demo_difference in the update statement.

Here is the corrected version of your stored procedure:

CREATE OR REPLACE PROCEDURE my_proc()
RETURNS STRING
LANGUAGE SQL
AS 
$$

-- Declare variables
DECLARE 
    demo_latest TIMESTAMP_LTZ;
    demo_current TIMESTAMP_LTZ;
    demo_difference INT;
    demo_current_week DATE;

BEGIN
    -- Set variables
    demo_latest := (SELECT MAX(begin_date_time) FROM demo_original_transactions_dateshift);
    demo_current := CURRENT_TIMESTAMP();
    demo_current_week := DATEADD(DAY, -DAYOFWEEKISO(CURRENT_DATE()) + 1, CURRENT_DATE());
    demo_difference := DATEDIFF('DAY', demo_latest, demo_current_week); 

    
    -- Update statements with date shifting
    UPDATE demo_original_transactions_dateshift
    SET begin_date_time = DATEADD(DAY, :demo_difference, begin_date_time),
        end_date_time = DATEADD(DAY, :demo_difference, end_date_time);

    -- Return a success message
    RETURN 'Update completed successfully';
END;
$$
;

Upon calling

enter image description here

Upvotes: 0

NickW
NickW

Reputation: 9798

You don't use an "=" sign to assign a value to a variable in SQL scripting. Try having another look at the documentation: https://docs.snowflake.com/en/developer-guide/snowflake-scripting/variables#assigning-a-value-to-a-declared-variable

Upvotes: 0

Related Questions