SnowFlake StoredProcedure Code not working when creating dynamic statement for information_schema.columns

Question

I am new to Snowflake, trying to create a small sp to get the row and col count of any table

SP Name : "GET_ROW_COUNT_TESTSP"("DATABASE_NAME" VARCHAR(16777216), "SCHEMA_NAME" VARCHAR(16777216), "TABLE_NAME" VARCHAR(16777216))

Snippets from body :

 var sql_command_columncount = "select COUNT (DISTINCT COLUMN_NAME) AS COLCOUNT from "+DATABASE_NAME+"."+"information_schema.columns where TABLE_CATALOG ="+''+DATABASE_NAME+''+ " and table_schema = " + ''+SCHEMA_NAME+''+ "and table_name = " + '' +TABLE_NAME+ ''+ "and column_name <> ''TESTCOLUMNNAME'' ";
  
  var stmt2 = snowflake.createStatement(
         {
         sqlText: sql_command_columncount
         }
      );
   var res2 = stmt2.execute();
   res2.next();
    COLCOUNT = res2.getColumnValue(1);
  return COLCOUNT;

This is throwing error, tried all different single quote, not working, hard-coding of dbname,schemaname,tablename is working.

Any help is appreciated.

Answer 1

There is a problem with quote encapsulation on that line. The best way to address this is to use backticks to enclose your SQL string. This will allow use of single and double quotes and line breaks without having to concatenate strings. It will also allow use of template literals, which means for any variable X you can replace its literal value in a string using the syntax ${X}. Your SQL statement would look something like this:

var sql_command_columncount = 
`select COUNT (DISTINCT COLUMN_NAME) AS COLCOUNT
from ${DATABASE_NAME}.information_schema.columns
where TABLE_CATALOG = '${DATABASE_NAME} 
and table_schema = '${SCHEMA_NAME}'
and table_name = ${TABLE_NAME}
and column_name <> 'TESTCOLUMNNAME'
`;

Answer 2

I agree with the above answer, there were a few missing quotes above so corrected it and below is the working code as tested.

CREATE OR REPLACE PROCEDURE "GET_ROW_COUNT_TESTSP"("DATABASE_NAME" 
VARCHAR(16777216), "SCHEMA_NAME" VARCHAR(16777216), "TABLE_NAME" 
VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
 $$
    var sql_command_columncount = `select COUNT (DISTINCT COLUMN_NAME) 
    AS COLCOUNT from ${DATABASE_NAME}.information_schema.columns where 
    TABLE_CATALOG = '${DATABASE_NAME}' and table_schema = 
    '${SCHEMA_NAME}'
     and table_name = '${TABLE_NAME}' and column_name <> 
     'TESTCOLUMNNAME'
      `
     var stmt2 = snowflake.createStatement(
     {
     sqlText: sql_command_columncount
     }
     );
    var res2 = stmt2.execute();
     res2.next();
      COLCOUNT = res2.getColumnValue(1);
     return COLCOUNT;
      $$;

Answer 3

Cleaner version without using ticks and used double quote and parameterized ...

create or replace procedure getrowcounts_1(DATABASE_NAME VARCHAR, SCHEMA_NAME VARCHAR, TABLE_NAME VARCHAR)
 RETURNS VARCHAR LANGUAGE JAVASCRIPT AS $$ 
  var sql_command="Select row_count  from "+DATABASE_NAME +"."+SCHEMA_NAME + "."+ "TABLES WHERE TABLE_NAME="+"'"+TABLE_NAME+"'";
     var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();   
 res.next(); 
  COLCOUNT = res.getColumnValue(1);
     return COLCOUNT;
 $$;

Output: