Snowflake stored procedure error - bind variable not set

user_1076100 0
Viewed 81

I have a stored procedure that tracks new records inserted in a table (change tracking is on). Pretty simple, code shown here:

create or replace procedure sp_audit_insert_counts(TABLENAME varchar, FILTER varchar, 

STARTTIME timestamp_ltz, ENDTIME timestamp_ltz)
returns integer
language sql
as
$$
declare 
iInsertCount integer default 0;
begin

select count(*) into :iInsertCount from identifier(:TABLENAME) changes(information => default) at(timestamp => :STARTTIME) end(timestamp => :ENDTIME) where metadata$action = 'INSERT' and metadata$isupdate = 'FALSE';

return iInsertCount;
end;
$$
;

However when I run the procedure, I get an error

Bind variable :STARTTIME not set

call sp_audit_insert_counts('TEST_TABLE', null, to_timestamp('2022-08-23 00:00:00'), to_timestamp('2022-08-23 09:00:00'))

If I hard code the time stamps in the procedure it work fine. What could be wrong?

Thanks in advance

Regards Sid

snowflake-cloud-data-platform stored-procedures sql
1 Answers

This is the best solution I could come up with:

set x_end = '2030-08-24 22:01:00'::timestamp;
set x_start = '1900-08-24 22:00:00'::timestamp;

create or replace procedure sp_audit_insert_counts(TABLENAME varchar, FILTER varchar, 
STARTTIME timestamp_tz, ENDTIME timestamp_tz)
returns integer
language sql
execute as caller
as
$$
declare 
iInsertCount integer default 0;
begin

set x_start = :STARTTIME;
set x_end = :ENDTIME;

select count(*) into :iInsertCount from identifier(:TABLENAME) changes(information => default) at(timestamp => $x_start) end(timestamp => $x_end) where metadata$action = 'INSERT' and metadata$isupdate = 'FALSE';


return iInsertCount;
end;
$$
;

To make it work I had to:

  • Create the variables before creating the stored procedure.
  • execute as caller to work with session variables.
  • Use timestamp_tz to avoid conversion weirdness.
user_1324380
Related