Using procedure parameters in the body to create statements dynamically

Question

I am trying to use a parameter given to a procedure in a statement that is created dynamically. I managed to do so but I believe this must be possible in a more "clean" way. The example given is the most minimal I could come up with in regards to the problem I am facing.

Setup:

create database test_db;
create schema test_schema;
create table test_db.test_schema.test_table(my_val varchar);

The following procedure works:

create or replace secure procedure test_db.test_schema.test_2(my_table varchar)
returns integer
language SQL
execute as caller
as
declare
    my_count integer default 1;
    lookup resultset;
    statement varchar;
begin
    statement := 'select count(*) as count from test_db.test_schema.' || :my_table || ';';
    lookup := (execute immediate :statement);
    
    let c1 cursor for lookup;
    for row_variable in c1 do
        my_count := row_variable.count;
    end for;
    return my_count;
end;


call test_db.test_schema.test_2('test_table');

However, here I need to create a resultset and iterate it using a cursor. This feels inefficient. So I tried to rewrite it into:

create or replace secure procedure test_db.test_schema.test_1(my_table varchar)
returns integer
language SQL
execute as caller
as
declare
    my_count integer default 1;
begin
    select count(*) into my_count from test_db.test_schema || :my_table;
    -- select count(*) into my_count from concat('test_db.test_schema', :my_table); 
    return my_count;
end;

Both solution with the || and the concat result in the error:

Syntax error: unexpected 'count'. (line 13)

Why is this, and do you see any other option to improve this code fragment? Please keep in mind that I need the result of the query to be stored in the my_count variable for further processing.

Answer 1

The procedure can be rewritten to avoid using cursor, dynamic SQL and SQL string concatenation:

'select count(*) as count from test_db.test_schema.' || :my_table || ';'
=>
-- "static query" with parametrized object name
select count(*) from IDENTIFIER(:variable); 

Code:

create or replace secure procedure test_db.test_schema.test_1(my_table varchar)
returns integer
language SQL
execute as caller
as
declare
    my_count INT;
    full_table_name text := ('test_db.test_schema.' || my_table);  
begin
    my_count := (select count(*) from IDENTIFIER(:full_table_name));

    return my_count ;
end;

Output:

CALL test_db.test_schema.test_1('test_table');
-- 0