How to Execute Stored procedure using 2 dynamic parameter in Direct query Method

[Ch_Sharma]

Hi Team,

In my project i have requirement to execute stored procedure using 2 dynamic parameter in Direct query method. I am able to execute the procedure using one parameter. But, when I try to execute 2 parameter i am getting error. 

Below is the screenshot of power M query code from advance editor. and error also attaching.

It would be really helpful if anyone of here can help me to fix this issue.

Thanks in advance.

[lbendlin]

count your single quotes. You are missing a couple and forgot to escape others.

[Ch_Sharma]

After checking all the quotes still i am getting the same error.

let
    CompanyId = Text.From(P_COMPANY_ID),  // Ensure P_COMPANY_ID is defined
    PeriodRange = Text.From(periodRange),  // Ensure periodRange is defined
    EscapedPeriodRange = "'" & Text.Replace(PeriodRange, "'", "''") & "'",  // Escape single quotes in periodRange
    Query = "SELECT * FROM OPENROWSET('SQLNCLI','Server=.......\XportUAT;Trusted_Connection=yes;','EXEC Xport_UAT_NEW.dbo.USP_GET_PERIOD_DATA_FS_BY_PERIODRANGE_SH @pCompany_Id=" & CompanyId & ", @PeriodRange=" & EscapedPeriodRange & "')",
    Source = Sql.Database("......\XportUAT", "Xport_UAT_NEW", [Query=Query])
in
    Source

I tried new above code. Could you please let me know where i am doing mistake. it would be helpful if you can suggest me in detail.

[lbendlin]

what's the purpose of 

SELECT * FROM OPENROWSET

 ?  

Looks like your SP is not even part of the query.

[Ch_Sharma]

Hi Ibendlin,

I am below attching SQL Stored Procedure.

SELECT * FROM OPENROWSET

This is used to call stored procedure by dynamic parameter in directQuery. As I tested simple excute funcition will not work for direct query.

below is the link which i have refred

https://prologika.com/power-bi-dynamic-m-query-parameters-reloaded/

can you guide me if my procedure is createing issue for calling procedure here?

ALTER proc [dbo].[USP_GET_PERIOD_DATA_FS_BY_PERIODRANGE_SH]                        

(                        

 @pCompany_id int=2308,  

 @periodRange nvarchar(10)                       

)                        

AS                        

BEGIN                        

 SET NOCOUNT ON                        

  declare @lFinalString  varchar(100),@lServerIp varchar(20),@lDbName varchar(50),@SQLQuery varchar(max), @ErpId int                        

  select @lServerIp = Server_IP,@lDbName = Db_name,@ErpId=ERP_ID from TBL_MAP_COMPANY_SERVER  where Company_Id = @pCompany_id                        

  set @lFinalString = '[' + @lServerIp +'].['+ @lDbName +'].dbo.'                        

CREATE TABLE #TEMP          

(PERIOD_IN_WORD VARCHAR(20), PRDENDDATE VARCHAR(20), PERIOD_ENDDATE VARCHAR(50))    

    if(@ErpId=1)    

        begin    

    SET @SQLQuery = 'Select  CASE WHEN SUBNUM=1 THEN ''One''            

   WHEN SUBNUM=2 THEN ''Two''            

   WHEN SUBNUM=3 THEN ''Three''             

   WHEN SUBNUM=4 THEN ''Four''            

   WHEN SUBNUM=5 THEN ''Five''            

   WHEN SUBNUM=6 THEN ''Six''            

   WHEN SUBNUM=7 THEN ''Seven''            

   WHEN SUBNUM=8 THEN ''Eight''            

   WHEN SUBNUM=9 THEN ''Nine''            

   WHEN SUBNUM=10 THEN ''Ten''            

   WHEN SUBNUM=11 THEN ''Eleven''            

   WHEN SUBNUM=12 THEN ''Twelve''             

END,CONVERT(VARCHAR,CAST(CAST(T_RefDate AS VARCHAR)AS DATE) , 106),format(T_RefDate,''MMMM dd, yyyy'') From ' + @lFinalString  + 'OFPR where (Code) = '''+@periodRange+''''          

End    

else if(@ErpId=2)    

begin    

 SET @SQLQuery = 'SELECT    

DISTINCT     

CASE WHEN CA.PERIOD=1 THEN ''One''            

   WHEN CA.PERIOD=2 THEN ''Two''            

   WHEN CA.PERIOD=3 THEN ''Three''             

   WHEN CA.PERIOD=4 THEN ''Four''            

   WHEN CA.PERIOD=5 THEN ''Five''            

   WHEN CA.PERIOD=6 THEN ''Six''            

   WHEN CA.PERIOD=7 THEN ''Seven''            

   WHEN CA.PERIOD=8 THEN ''Eight''            

   WHEN CA.PERIOD=9 THEN ''Nine''            

   WHEN CA.PERIOD=10 THEN ''Ten''            

   WHEN CA.PERIOD=11 THEN ''Eleven''            

   WHEN CA.PERIOD=12 THEN ''Twelve''

      WHEN CA.PERIOD=13 THEN ''Thirteen''      

   END AS [PERIOD_IN_WORD]    

,    

CONVERT(VARCHAR,CAST(CAST( CA.ENDDATE AS VARCHAR)AS DATE) , 106) AS [PERIODENDDATE]  ,  

format(cast(cast(CA.ENDDATE as varchar)as date),''MMMM dd, yyyy'') AS [PERIOD_ENDDATE]  

FROM ' + @lFinalString  + 'CSFSC     

CROSS APPLY    

(    

VALUES    

(1,BGNDATE1,ENDDATE1),    

(2,BGNDATE2,ENDDATE2),    

(3,BGNDATE3,ENDDATE3),    

(4,BGNDATE4,ENDDATE4),    

(5,BGNDATE5,ENDDATE5),    

(6,BGNDATE6,ENDDATE6),    

(7,BGNDATE7,ENDDATE7),    

(8,BGNDATE8,ENDDATE8),    

(9,BGNDATE9,ENDDATE9),    

(10,BGNDATE10,ENDDATE10),    

(11,BGNDATE11,ENDDATE11),    

(12,BGNDATE12,ENDDATE12),

(13,BGNDATE13,ENDDATE13) 

) AS CA (PERIOD,STARTDATE,ENDDATE)    

WHERE CSFSC.ACTIVE=1 and (''FY''+CONVERT(VARCHAR(4),CSFSC.FSCYEAR)+''-''+CONVERT(VARCHAR(2),FORMAT(CA.PERIOD,''00'')) ) = '''+@periodRange+''''       

end  

   INSERT INTO #TEMP EXEC (@SQLQuery)

 SELECT * FROM #TEMP          

  END 

GO

[lbendlin]

That link is irrelevant and/or misleading.  

Power Query and SQL Server Stored Procedures - BI Insight