CODEWITHSUNDEEP
sqlgoogle-bigquery
MOliver
MOliver

Reputation: 107

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?

Upvotes: 1

Views: 3273

Answers (2)

James McPherson
James McPherson

Reputation: 2556

Per https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#quoted_literals, triple-quoted strings allow you to have multi-line statements without line-continuation characters. Hit this myself just yesterday

Upvotes: 0

Rathish Kumar B
Rathish Kumar B

Reputation: 1412

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');

Upvotes: 2

Related Questions