Problem Bigquery stored procedure not filtering

I’m trying to use a stored procedure to get some data from my clients filtering a table by date and channel of purchase in a query that looks something like this:

CREATE OR REPLACE PROCEDURE `sandbox.clients.dateandchannel`
    (IN DT DATE, 
     IN CH STRING)
BEGIN
    SELECT 
        client_id,
        purchase_id,
        purchase_date,
        channel
    FROM 
        `trusted-zone.clients_purchases`
    WHERE 
        channel = CH
        AND purchase_date = DT;
END

After running the procedure, I use the following call:

CALL `sandbox.clients.dateandchannel`('2021-09-11','Ecommerce');

The call executes and returns a table, but the result is never filtered.

The dates don’t correspond to ‘2021-09-11’ and the channel doesn’t correspond to ‘Ecommerce’

I can’t understand if it is a problem with the query, or if I am doing something conceptually wrong but would love if someone had a clue to why this is happening.

Also the next step for me would be to extract the resulting table to a temp table or such to use this result in other steps of multiple queries. Inside a with, for example.

Thanks in advance

Answer

If you go back from the basics, from the semantic:

• Procedure execute code and return nothing, except the parameter in OUT mode
• Function execute code and return something.

So, the procedure will never send you back results, by design, by semantic!

---

If you need to pass parameter to something and to get a resultSet as answer you need a Functions. The table functions are made for that!