SQL Table Value Function with Parameters

[espo4life]

I have a Table-Value Function that I want to use as a data source and have it parameterized (even if I have two static parameters). The signature is

SELECT * FROM [dbo].[fnCUSTCLUBPLAN] (
<@PeriodStart, datetime2(7),>
,<@PeriodEnd, datetime2(7),>)

I imported it into Power BI with hardcarded dates and the editor shows it as

= dbo_fnCUSTCLUBPLAN(#datetime(2000, 1, 1, 0, 0, 0), #datetime(2000, 6, 5, 0, 0, 0))

Can anyone point me in the right direction?I have a parameter named "Parameter1" that is a date/time type.

Thanks in advance!

[v-yiruan-msft · Accepted solution]

Hi @espo4life ,

You can create 2 query parameters to filter the data based on the date field, please refer the contents in the following links to achieve it.

Working with Parameters in Power BI Desktop

How do I pass parameters to my SQL statement?

Best Regards

Rena

[espo4life]

That worked beautifully. Thank you!!

[TomMartens]

Hey @espo4life ,

this article describes what you are looking for:

https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-sql-native-query-wi...

Hopefully, this helps to tackle your challenge.

Regards,

Tom