[SOLVED] Create a query as a procedure in BigQuery

Create a query as a procedure in BigQuery

I am basically trying to do this MySQL script in BigQuery:

CREATE PROCEDURE `test` (IN tab_name CHAR(40) ) 
    BEGIN  
        SET @query = 
            CONCAT(
            'SELECT *
            FROM ', tab_name );
    PREPARE stmt FROM @query;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt; 
END

In theory, this should let me run this script:

CALL test(`my_dataset.my_table`)

and it will perform my "SELECT * FROM" script on that table.

So far, this isn't working for me because BQ doesn't want to accept the quotation marks breaking across lines, so it says there is an "Unclosed string literal".

Any idea how I can accomplish this in BQ?

Solution

Here is my attempt:

CREATE OR REPLACE PROCEDURE `mydataset.test`(tableName STRING)
BEGIN

DECLARE queryString STRING;

SET queryString = " SELECT * FROM mydataset."||tableName||"";

EXECUTE IMMEDIATE queryString;

-- SELECT queryString;
END;

Execute the procedure:

CALL `mydataset.test`('mytable');