Invalid Identifier when passing a string into sql query in snowflake

user_16879173 0
Viewed 278

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.

snowflake-cloud-data-platform
1 Answers

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');
user_50708790
Related