Snowflake SQL stored procedure and save value from query with dynamic SQL-CodePudding

I am writing a SQL stored procedure in Snowflake (language SQL NOT Javascript).

I am trying to save a count from a SELECT statement into a variable and the table name for the select statement needs to come from a variable.

Here is what I have so far but this is failing. I don't know where to put the USING or if I can even do this? I feel like I just don't have it syntactically correct yet.

create or replace procedure myprocedure(DBNAME varchar(16777216))
  returns int
  language sql
  as
  $$
    DECLARE
        excludeCount int;
        fullyQualifiedProceduresTable  varchar(16777216);    
        
    BEGIN
        fullyQualifiedProceduresTable := CONCAT(DBNAME, '.INFORMATION_SCHEMA.PROCEDURES');
        excludeCount := (SELECT count(*) as count from TABLE (?) WHERE PROCEDURE_OWNER = '<ROLE NAME>') USING fullyQualifiedProceduresTable ;
        
        IF (excludeCount > 0) THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;
$$;

CodePudding user response：

I am not sure why you need a procedure for this. Could you not do...

set table_name='abc';
set excludeCount=(select case when count(*)>0 then 1 else 0 end from identifier($table_name));

CodePudding user response：

This is what I got to work using slightly different syntax for using variables. Syntax taken from here

create or replace procedure myprocedure(DBNAME varchar(16777216))
  returns int
  language sql
  as
  $$
    DECLARE
        excludeCount int;
        fullyQualifiedProceduresTable  varchar(16777216);    
        
    BEGIN
        SELECT CONCAT(:DBNAME, '.INFORMATION_SCHEMA.PROCEDURES') into :fullyQualifiedProceduresTable;
        SELECT count(*) into :excludeCount from IDENTIFIER(:fullyQualifiedProceduresTable) WHERE PROCEDURE_OWNER = '<role>';
        
        IF (excludeCount > 0) THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;
$$;

CodePudding user response：

try this

call my_proc('bus_day');

create or replace procedure my_proc(table_name varchar)
returns table(a integer)
language sql
as
$$
  declare
    res RESULTSET;
    query varchar default 'SELECT count(*) FROM ' || :table_name ;
  begin
    res := (execute immediate :query);
    return table (res);
  end;
$$;