Forum Discussion

redexecutive's avatar
redexecutive
New Member
8 years ago
Solved

Stored Procedure parameters with dates

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's avatar
    v-yuta-msft
    8 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

    • redexecutive's avatar
      redexecutive
      New Member
      8 years ago

      Hi:

      Thanks for answer but throws this error

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

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    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's avatar
      redexecutive
      New Member
      8 years ago

      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's avatar
        v-yuta-msft
        Community Support
        8 years ago

        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