DirectQuery with stored procedure using temptables

Anonymous

Hello Lydia, 

For debugging purposes i'm not using the parameters. They do not influence the query what so ever. 

They are not used and will only be used if the I can get the Stored Procedure working with fixed hardcoded parameters. 

The stored procedure is quite complex so I condensed it to a minimum and called it GetDowntimePeriods3.

CREATE PROCEDURE [dbo].[GetDowntimePeriods3]
	@Start DATETIME = '2018-02-01',
	@Stop DATETIME = '2018-03-01',
	@MachineLineNr INT = 0
AS
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#ExtendedDowntimeLOG')	IS NOT NULL DROP TABLE #ExtendedDowntimeLOG
IF OBJECT_ID('tempdb..#DowntimeLOG')			IS NOT NULL DROP TABLE #DowntimeLOG

SELECT * INTO #DowntimeLOG
FROM BIT_Log
WHERE [TagID] = 445

SELECT 
	*,
	LEAD(Timestamp,1) OVER (ORDER BY Timestamp) as NextTS
	INTO #ExtendedDowntimeLOG
FROM #DowntimeLOG

UPDATE #ExtendedDowntimeLOG
SET Value = -1
WHERE [Timestamp] = NextTS

Select
	*,
	'Koekjeslijn 1' as LineName
From #ExtendedDowntimeLOG

drop table #DowntimeLOG
drop table #ExtendedDowntimeLOG

GO

The stored procedure has default parameters, primairily used for debugging purposes. This condensed version only uses data from one source: BIT_Log. A .csv file of the table can be fount here: LINK.

The text inside the Advanced Query Editor is the following:

let
    Source = Sql.Database(".", "MyDatabaseName", [Query="#(lf)#(lf)EXEC#(tab)[dbo].[GetDowntimePeriods3] WITH RESULT SETS ( ([Timestamp] Datetime, TagID INT, Value INT, NextTS Datetime, LineName VARCHAR(150)#(tab)));#(lf)#(lf)", HierarchicalNavigation=true])
in
    Source