Invalid Identifier when passing a string into sql query in snowflake

Question

I have a stored procedure which takes a string(SCHEMA_NAME) as a parameter
It then puts this string name into a query
The problem I am facing is when I pass the parameter into the sql query I get a
Invalid identifier error
Below is my code and what I have tried

CREATE OR REPLACE PROCEDURE "CREATE_SCHEMA"("SCHNAME" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
COMMENT='Creates schemas'
EXECUTE AS CALLER
AS 
$$
  var v_sqlCode = "select * from dbschemas where name = " + "''" + SCHNAME + "''";
  
  try{
    var sqlStmt = snowflake.createStatement({sqlText:v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
    errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
    errMessage += "\n  Message: " + err.message + v_sqlCode;
    errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
    throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
  }
$$;

I have tried writing my sql query in three ways
1. var v_sqlCode = `select * from dbschemas where name = `+ SCHNAME;
2. var v_sqlCode = "select * from dbschemas where name = " + "''" + SCHNAME + "''"
3.var v_sqlCode = `SELECT * FROM DBSCHEMAS WHERE NAME = {$SCHENAME}`

The way in which I call the stored procedure is as follows : CALL CREATE_SCHEMA('SCHEMA_NAME');

Any help would be greatly appreciated.

Lukasz Szozda · Accepted Answer · 2021-10-06 15:28:12Z

2

It is recommended to parametrize query to be executed instead of concatenating the SQL query string.

More info: Binding variables

CREATE OR REPLACE PROCEDURE CREATE_SCHEMA(SCHNAME VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
COMMENT='Creates schemas'
EXECUTE AS CALLER
AS 
$$
  var v_sqlCode = "select * from dbschemas where name = ?";
  
  try{
    var sqlStmt = snowflake.createStatement({sqlText:v_sqlCode, binds:[SCHNAME]});
    var sqlRS = sqlStmt.execute();
  }catch(err){
    errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
    errMessage += "\n  Message: " + err.message + v_sqlCode;
    errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
    throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
  }
$$;

Call:

CALL CREATE_SCHEMA('SCHEMA_NAME');

answered Oct 6, 2021 at 15:28

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

RMC_DEV Over a year ago

Thank you so so much:) I forgot about binding completely

Collectives™ on Stack Overflow

Invalid Identifier when passing a string into sql query in snowflake

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related