Snowflake SQL Procedure Parameters

user_12679828 0
Viewed 39

Apologies but I can't seem to find documentation on this.

I am passing in two strings converting them to date to find the number of rows. Its a silly question but how do I pass in parameters to a SQL query for cursor

CREATE OR REPLACE PROCEDURE LOAD_DATA("START_DATE" VARCHAR(12), "END_DATE" VARCHAR(12))
RETURNS FLOAT
LANGUAGE SQL
EXECUTE AS OWNER
AS '
declare
   row_count float;
    c1 cursor for select etl_year,etl_month,etl_day_of_year,1 as rowx
                 from dim_etldate
                 where etl_date >= to_date(start_date,''dd-mm-yyyy'')
                 and   etl_date <= to_date(end_date,''dd-mm-yyyy'');
begin
    row_count := 0.0;
    open c1;
    for rec in c1 do
        row_count := row_count + 1;
    end for;
    close c1;
    return row_count;
end;
';

Error is invalid identifier 'START_DATE'

snowflake-cloud-data-platform
1 Answers

If you use an argument in the SQL statement you need to put a colon : in front of it, like in this example (observe the id argument):

CREATE OR REPLACE PROCEDURE find_invoice_by_id(id VARCHAR)
RETURNS TABLE (id INTEGER, price NUMBER(12,2))
LANGUAGE SQL
AS
DECLARE
  res RESULTSET DEFAULT (SELECT * FROM invoices WHERE id = :id);
BEGIN
  RETURN TABLE(res);
END;

More information here.

user_77985480
Related