Stored Procedure parameters with dates

[redexecutive]

Really I don´t know how to manage this, I am trying to use a simple stored procedure (for now) with 2-date parameters.

CREATE PROC usp_Sel_Reporte_Indicadores @dtFechaIni DATE = NULL, @dtFechaFin DATE = NULL

AS

　

IF @dtFechaIni = NULL

SET @dtFechaIni = GETDATE()

IF @dtFechaFin = NULL

SET @dtFechaIni = GETDATE()

　

---Tiempo Disponible

SELECT *

FROM dbo.DC_VW_Paros

WHERE Fecha BETWEEN @dtFechaIni AND @dtFechaFin

this is my connection instruction:

let
    Source = Sql.Database(Sqlnstance, Database, [Query="EXEC usp_Sel_Reporte_Indicadores '" & Date.ToText(FechaIni,"yyyyMMdd") & "','" & Date.ToText( FechaFin,"yyyyMMdd")  & "'"])
in
    Source

With these 2 parameters

And in the Query Editor works correctly ,but when I close & apply changes show me this error

I appreciate any idea

[v-yuta-msft]

Hi redexecutive,

I have reproduced your issue, I made a simple table and created a procedure usp_Sel_Reporte_Indicadores with your script in SQL Server.

Fisrtly I tested import mode, I created 4 parameters like yours and created a new query with your script.

It worked well in Query Editor and after I clicked “close&apply” button there was no error.

However, when I test in direct query mode, I encountered the same error with you after clicking “apply” button like below:

 

After that I modified my SP with no input params, the error still existed. It seems that there are some limitations with SQL query in  direct query mode(e.g.:maybe don’t support execute SP)

I have sent a mail to PG to confirm this issue. Currently, as a workaround, you can use import mode instead of direct query mode.

Regards,

Jimmy Tao

[redexecutive]

Thanks v-yuta-msft for your quick answer, but my client need the information online, and there are a lot of calculations there. 😞

Regards

[v-yuta-msft · Accepted solution]

Hi redexecutive,

I have confirmed this issue from PG.  Actually, this is another limitation of DirectQuery that does not permit to use a store procedure.

Only queries with the form Select … From … Where … are allowed.

Best Regards,

Jimmmy

[redexecutive]

Thanks for the clarification 😀 I will not waste my time anymore in this solution type.

[Anonymous]

What happens if you change the date format from "yyyyMMdd" to "yyyy-MM-dd" ?

[redexecutive]

Hi:

Thanks for answer but throws this error

"Expression.Error: This native database query isn't currently supported."