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.
Related Articles,
- Handle Cursor in Snowflake Stored Procedures – Examples
- How to Create View Dynamically in Snowflake?
- Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs
- Snowflake Error handling – Procedure and Functions
- Snowflake NULL Handling Functions, Usage and Examples
Hope this helps 🙂