How to Create Stored Procedure with Big Query SQL

Create a Stored Procedure with Input Parameter

CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

select * from `Test.MyTestTable` where id = the_id;

END;

Execute the Procedure

DECLARE the_id STRING DEFAULT '2023-06-24 06:26:03.927610+00';
CALL `Test.sp_do_something`(the_id);

Create Procedure with Multi-statement

CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

create TEMP TABLE my_temp_table
(
  country string,
    device string,
    browser string
);

INSERT INTO
my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;

INSERT INTO
  `Test.MyTestTable` (id,items)
SELECT
  the_id,
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table;

select * from `Test.MyTestTable` where id = the_id;

END;

Run the Procedure has multi-statement

Now, notice the id has new record as test_100.

DECLARE the_id STRING DEFAULT 'test_100';
CALL `Test.sp_do_something`(the_id);

what if you run below SQL Script, it actually complain that table already exist.

CALL `Test.sp_do_something`('2000');
CALL `Test.sp_do_something`('400');

One of the way to fix is drop the temp table at the end of the Procedure, this should fix the previous issue.

drop TABLE my_temp_table;