Dynamic Sorting

We wish to specify the sorting columns dynamically i.e. through a variable.

As String Variable

The names of the column that we wish to sort by are specified as strings in an environment variable.

One Column

BEGIN
    DECLARE v_columns STRING;

    SET v_columns = 'col_1';
    EXECUTE IMMEDIATE FORMAT("""SELECT *
                                FROM refcon.dataset.table_1
                                ORDER BY %s
                             """, v_columns);
END;

Here is how this works:

  • We assign the sorting expression we want to use to v_columns variable.
  • We use EXECUTE IMMEDIATE to execute our dynamic query using the v_columns variable.
  • We achieve this using the FORMAT function which formats a data type expression as a string. %s will be replaced with the v_columns value.
  • The output of FORMAT will be SELECT * FROM refcon.dataset.table_1 ORDER BY col_1.

Multiple Columns

BEGIN
    DECLARE v_columns STRING;

    SET v_columns = 'col_1, col_2 DESC';
    EXECUTE IMMEDIATE FORMAT("""SELECT *
                                FROM refcon.dataset.table_1
                                ORDER BY %s
                             """, v_columns);
END;

Here is how this works:

  • This works similarly to the “One Column” scenario except that we mention multiple columns in our v_columns variable.
SQL
I/O