I'm a little new to Snowflake UI and Power BI Report Builder and was hoping to get some help after troubleshooting for a few days now. My problem is two fold.

So problem #1, essentially what I am trying to build in Power BI Report Builder is a report that allows the end user to enter xyz information and the report should spit out the the results via a table made in a stored procedure. The IT team at work has set up the ODBC driver for Snowflake and I've tested with simple queries to confirm that it works. I'm only having trouble calling the stored procedure from the PBI report builder and it has not worked - no matter what I have tried and someone suggested that in my stored procedure I should declare the fields in the RETURNS TABLE() section.

Here is where problem #2 occurs. I'm able to run my stored procedure fine in Snowflake UI when I do not define the fields in RETURNS TABLE(), but as soon as I try to declare and call the procedure, I get the following error:

Stored procedure execution error: data type of returned table does not match expected returned table type.

From testing, I've narrowed down that I'm unable to declare varchar fields in the RETURNS TABLE() statement, which does not make sense to me because clearly from this link, we should be able to declare, so I'm not sure what I'm doing wrong.

I've provided a simple test example of my procedure here:

CREATE OR REPLACE TABLE <schema>.TEST_TBL(a integer, b varchar, c varchar, d varchar);
INSERT INTO <schema>.TEST_TBL(a,b,c,d) VALUES
(1, 'Abigail', 'IA', '12345'),
(2, 'Brenda', 'CA', '66789'),
(3, 'Carter', 'PA', '01123');

CREATE OR REPLACE PROCEDURE <schema>.SP_Test("STATE" VARCHAR(2), "ZIP" VARCHAR(5))
RETURNS TABLE (a integer, b varchar)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
        res RESULTSET;
BEGIN
        RES: = (SELECT a, b from <schema>.TEST_TBL where c = :STATE and d = :ZIP);
        RETURN TABLE(res);
END;

CALL <schema>.SP_TEST('IA', '12345');

Based on the called procedure, I should be getting the first row from the table as an output, but instead I get the error above in the UI. If I can get this procedure working w/ the fields declared I can go back to testing to see if I can call it in Power BI report builder.

TLDR: Am I declaring the variables incorrectly in the RETURNS TABLE() statement of my stored procedure, and am I even able to call a Snowflake stored procedure in PBI report builder?

Appreciate any insight any of you may have, TIA!

I'm a little new to Snowflake UI and Power BI Report Builder and was hoping to get some help after troubleshooting for a few days now. My problem is two fold.

So problem #1, essentially what I am trying to build in Power BI Report Builder is a report that allows the end user to enter xyz information and the report should spit out the the results via a table made in a stored procedure. The IT team at work has set up the ODBC driver for Snowflake and I've tested with simple queries to confirm that it works. I'm only having trouble calling the stored procedure from the PBI report builder and it has not worked - no matter what I have tried and someone suggested that in my stored procedure I should declare the fields in the RETURNS TABLE() section.

Here is where problem #2 occurs. I'm able to run my stored procedure fine in Snowflake UI when I do not define the fields in RETURNS TABLE(), but as soon as I try to declare and call the procedure, I get the following error:

Stored procedure execution error: data type of returned table does not match expected returned table type.

From testing, I've narrowed down that I'm unable to declare varchar fields in the RETURNS TABLE() statement, which does not make sense to me because clearly from this link, we should be able to declare, so I'm not sure what I'm doing wrong.

I've provided a simple test example of my procedure here:

CREATE OR REPLACE TABLE <schema>.TEST_TBL(a integer, b varchar, c varchar, d varchar);
INSERT INTO <schema>.TEST_TBL(a,b,c,d) VALUES
(1, 'Abigail', 'IA', '12345'),
(2, 'Brenda', 'CA', '66789'),
(3, 'Carter', 'PA', '01123');

CREATE OR REPLACE PROCEDURE <schema>.SP_Test("STATE" VARCHAR(2), "ZIP" VARCHAR(5))
RETURNS TABLE (a integer, b varchar)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
        res RESULTSET;
BEGIN
        RES: = (SELECT a, b from <schema>.TEST_TBL where c = :STATE and d = :ZIP);
        RETURN TABLE(res);
END;

CALL <schema>.SP_TEST('IA', '12345');

Based on the called procedure, I should be getting the first row from the table as an output, but instead I get the error above in the UI. If I can get this procedure working w/ the fields declared I can go back to testing to see if I can call it in Power BI report builder.

TLDR: Am I declaring the variables incorrectly in the RETURNS TABLE() statement of my stored procedure, and am I even able to call a Snowflake stored procedure in PBI report builder?

Appreciate any insight any of you may have, TIA!

• stored-procedures
• snowflake-cloud-data-platform
• powerbi-datasource

1 Answer 1

Your stored procedure works for me with declaring the variables in RETURNS TABLE, I just made a small change to remove the space after : RES: = which becomes RES:=

Create the procedure as your given example

CREATE OR REPLACE PROCEDURE TEST.SP_Test("STATE" VARCHAR(2), "ZIP" VARCHAR(5))
RETURNS TABLE (a integer, b varchar)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
        res RESULTSET;
BEGIN
        RES:= (SELECT a, b from TEST.TEST_TBL where c = :STATE and d = :ZIP);
        RETURN TABLE(res);
END;

Calling the procedure

CALL TEST.SP_TEST('IA', '12345');

Results