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

Question

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;
$$;

David Garrison · Accepted Answer · 2022-02-11 22:33:14Z

1

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;
$$;

edited Feb 11, 2022 at 22:33

answered Feb 11, 2022 at 18:26

David Garrison

2,9101 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Garrison Over a year ago

The other answers suggest reasonable alternatives, but I wanted to specifically address the question of getting it to work as is with fixing the syntax.

David Garrison Over a year ago

Updated this with a minor tweak to use the "IDENTIFIER" fucntion instead of "TABLE" function. I think it's a bit of an improvement when your "table" function is just a wrapper around a table name. Thanks to Phil Coulson below for the idea.

Rajat · Accepted Answer · 2022-02-11 18:00:09Z

1

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));

answered Feb 11, 2022 at 18:00

Rajat

5,8733 gold badges14 silver badges30 bronze badges

2 Comments

Laura Beranek Over a year ago

I do need a stored procedure as it is doing a lot more than this one thing. I just asked a question specifically about one thing in a bigger stored proc.

David Garrison Over a year ago

somehow. I hadn't seen the identifier syntax before. That could definitely be super useful. Thanks for the introduction to it ;)

Himanshu Kandpal · Accepted Answer · 2022-02-11 18:24:51Z

0

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;
$$;

answered Feb 11, 2022 at 18:24

Himanshu Kandpal

1,67611 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related