DBMS_OUTPUT in Stored Procedures: During development of snowflake procedures, we need to debug the code, display output ,identify the reason why it is getting failed, producing debugging information on the screen. In order to debug the code we generally follow the RETURN statement or log the output/error inside the table. Once the procedure finish, traverse the table to identify the values of variables fetch in resultset.
Limitation with RETURN is when you use in program it stops the execution. So, assume you have a source table having high number of columns and you want to verify the values among these columns. Either you can combine all values in a string and return the concatenated output or use return statement for every individual column.
As we seen in ORACLE, we have DBMS_OUTPUT.PUT_LINE package to send messages from procedures or to display output on a screen. It builds a line of information piece by piece and helpful to display values and text at different points in your code. Currently, Snowflake is lagging in displaying output. In snowflake stored procedures we can use the ARRAY and build the output information in KEY:VALUE pair. Later on we can return this entire array as VARIANT.
So we have developed the following procedure using dbms_output  an array. This array will holds the details piece by piece and accumulate all output in KEY:VALUE pair. Please note the output of the procedure we have returned is of VARIANT Type. By using ARRAY.PUSH method we are pushing all checkpoint details inside the ARRAY.
Once the procedure gets created, run the proc and observe the below output in JSON format.
Click on the output and you will see the detailed output in below way. We can clearly see how the custom output has been build and it will help us to debug the code easily in one go.