How to Print SQL Query in Snowflake Stored Procedure?

Post author:Vithal S
Post last modified:April 20, 2023
Post category:Snowflake
Reading time:4 mins read

As explained in my other article, to support migration from other relational databases, Snowflake supports the stored procedures. Snowflake uses JavaScript as a procedural language. It provides many features including control structures – branching, looping, Dynamic SQL, error handling, etc. But, JavaScript API, does not provide any print statement support to display the content of variable or SQL query itself. In this article, we will check how to print SQL query in Snowflake stored procedure.

Print SQL Query in Snowflake Stored Procedure

Snowflake stored procedure support many useful JavaScript API’s. But, it does not provide standard JavaScript console.log method. However, it does support JavaScript API, getSqlText(), that you can use to return the SQL text of the prepared query in statement object.

Following is the syntax of getSqlText() JavaScript API. The returns a string of the prepared query text.

var queryText = statement.getSqlText();

Following example demonstrates the use of getSqlText() API to return the dynamically created query text.

create or replace procedure getQueryText(table_name VARCHAR)
    returns varchar 
    not null
    language javascript
    as
    $$
    var row_count = 0;
    // Dynamically compose the SQL statement to execute.
    var sqlCommand = "select count(*) from " + TABLE_NAME;
    // Prepare statement.
    var stmt = snowflake.createStatement({sqlText: sqlCommand});
	var rs = stmt.execute()
	// get SQL text from Statement
	var queryText = stmt.getSqlText();
    return queryText;
    $$
    ;

Following is the output.

call getQueryText('EMPLOYEE');
+-------------------------------+
| GETQUERYTEXT                  |
|-------------------------------|
| select count(*) from EMPLOYEE |
+-------------------------------+

Please note that, this method does not act as a print statement. But, it will definitely allow you to return the SQL text that you can use to debug if any issues before executing it on Snowflake.