Use SQL Store Procedure in Power BI

Openquery did not work for us as we have an SP that utilized a temp table.

but we have had some success using "with result sets" option of exec when that issue comes up.

for instance

SELECT *
FROM OPENQUERY ("snapserver", 
'EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))') 

This seems to work without issue. Not always ideal as it requires changing the result set definition if the query changes but as these are bi reports they are likely using the same output scheme for most queries.

the second half of this is trying to get parameters to work.

i think that this would work if the declare statement is removed. bi doesnt seem to like thing like declare or with unless part of the dynamic sql which wont work in this case.

I assume the declare statement can be removed and @team replaced with a bi parameter. but i have not tried it yet.

declare @team nvarchar(max)= 'winet'
exec ('
SELECT *
FROM OPENQUERY ("snapserver", 
''EXEC specialprojects.dbo.CFE_DASHBOARD_Summary_BugTrendX @team='+@team+'
WITH RESULT SETS
(
( [rel] nvarchar(16) NOT NULL,
  [value] int NOT NULL,
  [year] int NOT NULL,
  [series] nvarchar(16) NOT NULL,
  [sortorder] int NOT NULL
))'')')