Debugging SQL stored procedures in Snowflake

Introduction

Sometimes, you may have some complex queries with CTEs in a Snowflake SQL Stored Procedure. In SQL Server, I was used to use some simple print statements to debug code, or copy the complete code into another window and run the code.  I haven't found an equivalent for this in Snowflake but I learned  today a way how to debug SQL code with some variables in a stored procedure.

The problem is that when you copy SQL from the stored procedure with variables you need to include a block but then this returns a null, but you want a result back. But may be it's my lack of understanding of Snowflake and there are better ways to do it. Let me know or leave a comment.

Debug the code

For example, you could have a stored procedure like is written below. We have some variables defined with a LET and some complex queries with 5 CTE's and a lot of variables used in the queries.

CREATE OR REPLACE TABLE etcetc(id integer, value string);

--CREATE PROCEDURE Blabla
BEGIN
	LET Othervariable STRING := 'Hello';
    
    INSERT INTO etcetc
    SELECT 1,
       :Othervariable    AS Value;
END	

And what happens if you want to copy the query from the stored procedure into a new worksheet, for instance? Let's find out.

LET Othervariable STRING := 'Hello';

SELECT 1,
  :Othervariable    AS Value;
       

You'll get an error:

Ok now let's put a BEGIN and an END around the code :

BEGIN
    LET Othervariable STRING := 'Hello';

    SELECT 1,
      :Othervariable    AS Value;
END

But this will give nothing back :

The next step could be : including a result set in the block and return the resultset back. 

BEGIN 		
	LET res RESULTSET;
	LET Othervariable STRING := 'Hello';
	RES := (
        SELECT 1,
        :Othervariable    AS Value
	);

	RETURN TABLE(RES);

END	

Resulting in the following result. Now the block gives me a result back that I can debug and understand. 

So my debug code is shown her below in bold. Now I can copy the query between the ( and ) and include the variable declarations and debug my complex queries.

BEGIN 		
	LET res RESULTSET;
	LET Othervariable STRING := 'Hello';
	RES := (
            SELECT 1,
            :Othervariable    AS Value
	);

	RETURN TABLE(RES);

END	

It's still not what I really want but it is something can help to debug code and you don't need to change the code. 

Final thoughts

I hope you find this useful. As I said before, may be it's my lack of understanding of de Snowflake coding but I do find it sometimes cumbersome to debug stored procedure in Snowflake. It is easier in SQL Server to do (for so far I know).

Hennie