How to Use Big Query SQL Procedure

BQ SQL - create procedure without parameter

Create Procedure has no input or output parameter

CREATE OR REPLACE PROCEDURE `Test.sp_without_parameter`()

begin
select 'test';
end;

Run the no input, output procedure.

CALL `Test.sp_without_parameter`();

BQ SQL - create procedure with input and output parameter

Below script will do a simple calculation with x plus y and return the total.

CREATE OR REPLACE PROCEDURE `Test.sp_with_input_output`
(
 x int64,
 y int64,
 OUT total int64
)

begin
set total = x + y;
end;

This's how we can call the procedure to get the result.

declare total int64;
CALL `Test.sp_with_input_output`(10, 20, total);
select total;

Next, how to make Procedure return Array Type data?

In order for BQ Sql to return array like type, we can use Array with Struct.

First, let's create a procedure return the array custom type, in below example we want to return list of items contains id, country, device and browser fields.

As you can see the return type is OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>

CREATE OR REPLACE PROCEDURE `Test.sp_with_output_record`(OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>)
begin
create TEMP TABLE my_temp_table
(
  id int64,
  country string,
  device string,
  browser string
);

insert into my_temp_table
select 1 as id, 'US' as country, 'mobile' as device, 'safari' as browser union all
select 1 as id, 'CA' as country, 'desktop' as device, 'chrome' as browser
;

set items =
array(select struct(id, country, device, browser) from my_temp_table);

drop table my_temp_table;
end;

Next, we'll calling the stored procedure to get the list of country, device and browser.

DECLARE items ARRAY<struct<id int64, country STRING, device STRING, browser STRING>> DEFAULT NULL;
CALL `Test.sp_with_output_record`(items);

Last, we want to join the items with another table to get the result.

select * from unnest(items) as i
inner join `Test.TestLookup` as t
on i.id = t.id

The TestLookup table is a simple lookup table contains below values.

id | name
1 | my name

Above is how you can call BQ Stored Procedure to get custom Array type, this is useful so you can move long procedure into small ones for its own responsibility.