0
create or replace procedure test_table_proc(ingestion varchar)
returns varchar 
language javascript
as
$$

var step2="create or replace temporary table FN_IGSN_ROLE_PLAYER_NAME_OUTPUT_TEMP"
step2+=ingestion 
step2+=" as select * from bv_principal limit 10";

var statement2=snowflake.createStatement( {sqlText: step2,binds: [ingestion]} );
statement2.execute();
     
$$
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
Nagendra
  • 171
  • 2
  • 12
  • I think the main issue here is that Snowflake's SP will treat all parameter names UPPER cased, even though you used lower case in the parameter name. So ingestion should really "INGESTION". Please check the doc here: https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#stored-procedure-or-udf-unexpectedly-returns-null. And as Greg mentioned, you did not have any bind variables defined in the query, so no need for "binds". – Eric Lin Oct 30 '21 at 10:31

1 Answers1

0
create or replace procedure test_table_proc("ingestion" varchar)
returns varchar 
language javascript
execute as owner
as
$$

let step2 =
`
create or replace temporary table FN_IGSN_ROLE_PLAYER_NAME_OUTPUT_TEMP_${ingestion}
as select * from bv_principal limit 10
`;

var statement2=snowflake.createStatement( {sqlText: step2} );
statement2.execute();
     
$$;

call test_table_proc('SUFFIX');

You can't bind a variable to a partial table name, so you can remove the bind. The string concatenation will get untidy quickly, so you can use backticks ` to open and close your strings and use ${variableName} syntax to replace values. I also recommend using let to define variables unless you understand the difference between let and var.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Please provide snowflake document url to understand let and var – Nagendra Oct 28 '21 at 15:08
  • It's not so much a Snowflake thing as it is a JavaScript thing. https://stackoverflow.com/questions/762011/whats-the-difference-between-using-let-and-var – Greg Pavlik Oct 28 '21 at 15:33