Importing Stored Procedure + Dynamic Date Parameter

[andyor]

I'm new to to Power BI and I've been researching how to import Stored Procedure's from Get Data query in PBI and someone said that the easiest way is to Execute in SQL Server, then copy in the execution code, as below.

DECLARE	@return_value int

EXEC	@return_value = [Reporting].[WallerNumbers]
		@TimeZoneID = 1,
		@TZStartDate = @start,
		@TZEndDate = @end,
		@DateType = 3

SELECT	'Return Value' = @return_value

EXEC	@return_value = [Reporting].[XXXXX]
		@TimeZoneID = 1,
		@TZStartDate = N'2022-09-01',
		@TZEndDate = N'2022-09-12',
		@DateType = 3

SELECT	'Return Value' = @return_value

This works fine but I have 2 questions from here:

1) Is this the best way to import into Power BI from a Stored Procedure?

2) How can I manipulate this to have the TZEndDate paramater be dynamic i.e.., getdate()? If I was using SSRS I would hard code this in the report parameters

[andyor · Accepted solution]

The solution is to declare and set these in the import statement E.g., 

DECLARE	@return_value int
DECLARE @start datetime
DECLARE @end datetime

SET @start = DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))
SET @end = getdate()

EXEC	@return_value = [Reporting].[XXXXX]
		@TimeZoneID = 1,
		@TZStartDate = @start,
		@TZEndDate = @end,
		@DateType = 3

SELECT	'Return Value' = @return_value