Invalid Identifier Problem in my stored procedure

Question

I am trying to run below code but I am getting Invalid identifier 'RECORD.SALES_DATE' error when I call the procedure.

Below is my query

CREATE OR REPLACE PROCEDURE check_sales_and_logs()
RETURNS STRING
LANGUAGE SQL
AS
DECLARE
new_sales_table STRING := 'sales_new';
sales_table STRING := 'sales';
product_table STRING := 'products';
logs_table STRING := 'logs';
product_id NUMBER;
sales_date date;
product_exists NUMBER;
sales_record CURSOR FOR SELECT * FROM sales_new;
BEGIN
FOR record IN sales_record DO
    product_id := record.product_id;
    SELECT COUNT(1) INTO product_exists FROM products WHERE products.product_id = product_id;

    IF (product_exists > 0)THEN
     INSERT INTO sales (sales_date, product_id, quantity, customer_id) 
     VALUES (record.sales_date, record.product_id, record.quantity, record.customer_id);

    ELSE
        INSERT INTO logs (sales_date, product_id, quantity, customer_id) 
        VALUES (record.sales_date, record.product_id, record.quantity, record.customer_id);
    END IF;
END FOR;
RETURN 'Procedure completed successfully.';
END;

Actually I have 4 tables named as sales_new, sales, products and logs. I just want to check for every record in the sales_new table that if the product_id from sales_new table is present in the products table then that record should be added to my existing sales table and if the product_id is not present in the products table then it should put that record in the logs table.

Answer 1

In order to reference variable inside SQL statement they have to be prefixed with :

Using a Variable in a SQL Statement (Binding)¶

You can use a variable in a SQL statement. (This is sometimes referred to as binding a variable.) Prefix the variable name with a colon. For example:

INSERT INTO my_table (x) VALUES (:my_variable)
                                 ^

---

The provided code sample does not use this syntax:

IF (product_exists > 0)THEN
 INSERT INTO sales (sales_date, product_id, quantity, customer_id) 
 VALUES (record.sales_date, record.product_id, record.quantity, record.customer_id);

ELSE
  INSERT INTO logs (sales_date, product_id, quantity, customer_id) 
  VALUES (record.sales_date, record.product_id, record.quantity, record.customer_id);
END IF;

Currently (version: 7.6.4) it is not possible to prefix For-Cursor loop variable and none of below versions will work:

• record.sales_date
• :record.sales_date
• record.:sales_date
• :record.:sales_date
• :(record.sales_date)

To reference the cursor variable you need to reassign it to helper variable(type has to be explicitly provided):

FOR record IN sales_record DO
    LET product_id INT   := record.product_id;
    LET sales_date DATE  := record.sales_date;
    LET quantity INT     := record.quantity;
    LET customer_id INT  := record.quantity;

    -- ... 
    INSERT INTO sales (sales_date, product_id, quantity, customer_id) 
    SELECT :sales_date, :product_id, :quantity, :customer_id;
 END FOR;

---

Snowflake Ideas - Search for: "Direct for-cursor loop variable reference with SQL statements"

The idea is to allow syntax like: :record.sales_date