Snowflake - SP returning 0 on count when should be returning 1

Question

i have the next two procedures in snowflake:

CREATE OR REPLACE PROCEDURE db.schema.sp_test_1()
RETURNS VARCHAR
LANGUAGE SQL 
AS
$$
DECLARE
    db_name varchar(20) DEFAULT 'DB1';
    schema_name varchar(30) DEFAULT 'SCHEMA1';
    table_name varchar(50);
    col_name varchar(60);
    response integer;
BEGIN
  table_name := 'TABLE1';
  col_name := 'COL1';
  response := (call sp_test_2(:db_name,:schema_name,:table_name,:col_name));
RETURN response;
END;
$$;


CREATE OR REPLACE PROCEDURE db.schema.sp_test_2(db_na VARCHAR, schema_na VARCHAR, table_na VARCHAR, col_na VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
AS
$$
  DECLARE
    value INTEGER;
    res1 resultset DEFAULT (SELECT COUNT(*) AS COUNT FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_CATALOG" = :db_na AND "TABLE_SCHEMA" = :schema_na AND "TABLE_NAME" = :table_na AND "COLUMN_NAME" = :col_na);
    c1 CURSOR FOR res1;
  BEGIN
      FOR row_variable IN c1 DO
          value := row_variable.count;
      end for;
    RETURN value;
  END;
$$;

If i execute the select count outside of the sp, returns 1 row with the value 1 (column already exists in the table) but when i call the first sp: call db.schema.sp_test_1(); it returns 0.

I have tried a bunch of different thinks but nothing works, i tried identifier(:db_na) etc. but doesn't work!

Help please, thanks

Answer 1

In db.schema.sp_test_2(), change the line

  res1 resultset DEFAULT (SELECT COUNT(*) AS COUNT FROM "INFORMATION_SCHEMA"."COLUMNS" WHERE "TABLE_CATALOG" = :db_na AND "TABLE_SCHEMA" = :schema_na AND "TABLE_NAME" = :table_na AND "COLUMN_NAME" = :col_na);

to

view_na varchar DEFAULT CONCAT(:db_na,'.INFORMATION_SCHEMA.COLUMNS');
res1 resultset DEFAULT (SELECT COUNT(*) AS COUNT FROM IDENTIFIER(:view_na) WHERE "TABLE_CATALOG" = :db_na AND "TABLE_SCHEMA" = :schema_na AND "TABLE_NAME" = :table_na AND "COLUMN_NAME" = :col_na);