*args in BigQuery Stored Procedure

Question

Is there any concept of *args in BigQuery parameters (similar to pytho *args)

I am writing a generic stored procedure that will check if the table has duplicate rows. Obviously the number of columns will vary & given at the run time.

So I want to use *args to receive arguments, unpack them & use them in dynamic SQL.

CREATE OR REPLACE PROCEDURE `my_project.LOGGING.check_dup_prc`(project_name STRING, data_set_name STRING, table_name STRING, date_id DATE, *args)
BEGIN
  DECLARE sql STRING;
  set sql ='Select date,*args,count(1) from `'||project_name||'.'||data_set_name||'.'||table_name|| '` where date=\''||date_id || '\' GROUP BY date,*args HAVING COUNT(*)>1';
  EXECUTE IMMEDIATE (sql);
END;

Thanks in advance for your reply.

Answer 1

Looks like you want to be able to pass to proc list of the columns that will be used in SELECT as well as GROUP BY. In this case, instead of passing list of separate dolumns you can just pas one parameter which will hold list of those columns as a one string as in below example

So, it will be like below (the rest of syntax is left as it is in the question)

CREATE OR REPLACE PROCEDURE my_project.LOGGING.check_dup_prc(project_name STRING, data_set_name STRING, table_name STRING, date_id DATE, cols STRING) 
BEGIN DECLARE sql STRING; 
set sql ='Select date,'||cols||','||'count(1) from '||project_name||'.'||data_set_name||'.'||table_name|| ' where date=\''||date_id || '\' GROUP BY date,'||cols||'HAVING COUNT(*)>1'; 
EXECUTE IMMEDIATE (sql); END;

and call will look like below

CALL my_project.LOGGING.check_dup_prc(my_project, my_project,my_table,CURRENT_DATE(),'col1,col2,col3,col4');