My BigQuery Stories — Dynamically passing filters to BigQuery Stored Procedure

In this article of My BigQuery Stories — I would like to share one of common use cases involving a Stored procedure.

There are always instances where in you have created a stored procedure which needs to evolve with the changing data and hence we need to make the procedure reusable by dynamically passing the “condition” upon which the stored procedures yields a particular outcome.

Once such instance being, in one my recent projects, I had created a stored procedure to INSERT some data into a BigQuery table. I had to change the procedure to accept parameter which is passed as an condition to execute the INSERT statement.

Below statement gives away the approach in which I made this work.

Stored Procedure code:

CREATE OR REPLACE PROCEDURE myproject.zz_DataSet.procTest
                            ( title_name STRING
                             , condition1 string
                             , condition2 string)
BEGIN

  EXECUTE IMMEDIATE format ("""
    INSERT INTO myproject.zz_DataSet.zzProcTest (TITLE_NAME)
    SELECT firstname
    FROM myproject.zz_DataSet.tblOne
    WHERE "condition1" = if(@c1 is null,@c2 ,@c1)
    """
  )
  USING condition1 as c1,
        condition2 as c2;
END;

Call to procedure:

In this call to the procedure, the case condition 1 is evaluated to true and hence INSERTS condition 1 in table tblOne.

call myproject.zz_DataSet.procTest("condition1","condition1","")

Hope this helps. Look out for more my BigQuery learnings and stories coming ahead..