1

I am trying to call the below procedure and I'm getting an unexpected ':' error due to binding variable. If I am using simple JS concatenation instead,its working. But please let me know if there is any alternative using binding variables.Also in which all part of a query binding variables will work?

CREATE OR REPLACE PROCEDURE test_proc ()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
var V_TEMP = '123'
var V_SQL = `CREATE TABLE TEST_TABLE:1  AS
SELECT  A.FIRST_NAME,
A.LAST_NAME
FROM    
MARKET A
WHERE  first_name>3000;`
var EXEC_V_SQL = snowflake.createStatement(
{
sqlText: V_SQL, binds: [ V_TEMP ]
}
)
var result1 = EXEC_V_SQL.execute();
$$; ```
Hyma R Nair
  • 13
  • 5

1 Answers1

0

The issue is you are using a bind variable for an object name without using the IDENTIFIER() function. Try something like this instead:

CREATE OR REPLACE PROCEDURE test_proc ()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
var V_TEMP = 'TEST_TABLE123'
var V_SQL = `CREATE TABLE IDENTIFIER(:1)  AS
SELECT  A.FIRST_NAME,
A.LAST_NAME
FROM    
MARKET A
WHERE  first_name>3000;`
var EXEC_V_SQL = snowflake.createStatement(
{
sqlText: V_SQL, binds: [ V_TEMP ]
}
)
var result1 = EXEC_V_SQL.execute();
$$;
Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • Thanks for your input..this way is working when we can put the whole table name in the variable.What if I am generating the V_TEMP value dynamically during procedure and want to create a table with that value appended to "TEST_TABLE" like TEST_TABLE123 – Hyma R Nair May 20 '21 at 10:35
  • You can leverage the bind variable to create a string in javascript and then use that string in your table name in the same manner (using IDENTIFIER) and that will work, too. You do need to have a single variable inside the IDENTIFIER, though, whether its a bind or a calculated string. You can't calculate inside the function. – Mike Walton May 20 '21 at 21:32