BigQuery Stored Procedure SQL%RowFound

Question

How do I check if SQL statement within stored procedure returned one or more rows successfully. Following is my stored procedure where I am checking if a particular table has duplicates. I am not sure how to write IF statement within stored procedure

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,OUT found_duplicates 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); 

if SQL%ROWSFOUND THEN
   found_duplicates ='Y'
ELSE 
   found_duplicates='N'
END;

END;

Answer 1

I'd suggest to write the sql as:

DECLARE rowsFound BOOL;

set sql ='Select count(*) > 0 from '||project_name||'.'||data_set_name||'.'||table_name|| ' where date=@date_id GROUP BY date,'||cols; 
EXECUTE IMMEDIATE (SQL) INTO rowsFound USING date_id AS date_id; 

Note that date_id is also passed in as a parameter @date_id, this is always recommended.

You can also find more about INTO and USING for EXECUTE IMMEDIATE from public documentation.

You should also adjust the sql to make sure it only spits out 1 row to make INTO work.

Update: in case you need to capture more than one row

DECLARE rowsFound ARRAY<BOOL>;

SET sql ='Select <some_array>'; 
EXECUTE IMMEDIATE (SQL) INTO rowsFound;