1

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.

1 Answer 1

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'); 
Sign up to request clarification or add additional context in comments.

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.