Error while fetching data from stored procedure in Snowflake

[powerbiexpert22]

i am getting below error which fetching data from snowflake procedure, . procedure is basically display all data from snowflake table . below is the code of snowflake procedure

 

 

CREATE OR REPLACE TABLE EMPLOYEE_SAMPLE (
EMP_ID INT,
EMP_NAME STRING,
DEPARTMENT STRING,
SALARY NUMBER(10,2)
);

INSERT INTO EMPLOYEE_SAMPLE (EMP_ID, EMP_NAME, DEPARTMENT, SALARY) VALUES
(1, 'Amit Sharma', 'IT', 75000),
(2, 'Neha Verma', 'HR', 60000),
(3, 'Rahul Singh', 'Finance', 82000),
(4, 'Priya Mehta', 'IT', 90000);

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE_DATA()
RETURNS TABLE (
EMP_ID INT,
EMP_NAME STRING,
DEPARTMENT STRING,
SALARY NUMBER(10,2)
)
LANGUAGE SQL
AS
$$
DECLARE rs RESULTSET;
BEGIN
rs := (SELECT * FROM EMPLOYEE_SAMPLE);
RETURN TABLE(rs);
END;
$$;

CALL GET_EMPLOYEE_DATA();

[v-hjannapu · Accepted solution]

Hi @powerbiexpert22,

Thank you  for reaching out to the Microsoft fabric community forum.

The problem is coming because Power BI Snowflake connector does not support calling stored procedures using CALL statement in the SQL box. Power BI expects a normal SELECT query, table or view. Since CALL GET_EMPLOYEE_DATA() is not a SELECT statement, Power BI is not able to read the metadata properly and Snowflake throws that compilation error. So this is not a table or permission issue, it is just a limitation from Power BI side. To fix this, you can either create a view on top of the table and use that in Power BI, or convert the logic into a table function, or simply use a direct SELECT query if the requirement is only to display table data.

Hope the above information helps you to resolve the issue. If you still have any questions or need further clarification, please let us know.
Regards,
Community Support Team.

[amitchandak]

@powerbiexpert22 , As suggested on old post try Native Query

https://community.fabric.microsoft.com/t5/Desktop/Calling-snowflake-stored-procedure-from-Power-Bi-d...