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

Question

Hi Team,&nbsp;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.&nbsp;Below is the screenshot of power M query code from advance editor. and error also attaching.&nbsp;It would be really helpful if anyone of here can help me to fix this issue.Thanks in advance.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

lbendlin · Answer

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

anonymous · Answer

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 = "'" &amp; Text.Replace(PeriodRange, "'", "''") &amp; "'",  // 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=" &amp; CompanyId &amp; ", @PeriodRange=" &amp; EscapedPeriodRange &amp; "')",
    Source = Sql.Database("......\XportUAT", "Xport_UAT_NEW", [Query=Query])
in
    SourceI 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 · Answer

what's the purpose of&nbsp;
&nbsp;
SELECT * FROM OPENROWSET
&nbsp;?&nbsp;&nbsp;
&nbsp;
Looks like your SP is not even part of the query.

anonymous · Answer

Hi Ibendlin,I am below attching SQL Stored Procedure.SELECT * FROM OPENROWSETThis is used to call stored procedure by dynamic parameter in directQuery. As I tested simple excute funcition will not work for direct query.&nbsp;below is the link which i have refredhttps://prologika.com/power-bi-dynamic-m-query-parameters-reloaded/&nbsp;can you guide me if my procedure is createing issue for calling procedure here?&nbsp;&nbsp;ALTER proc [dbo].[USP_GET_PERIOD_DATA_FS_BY_PERIODRANGE_SH]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;@pCompany_id int=2308,&nbsp;&nbsp;&nbsp;@periodRange nvarchar(10)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;AS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;BEGIN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;SET NOCOUNT ON&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; declare @lFinalString&nbsp; varchar(100),@lServerIp varchar(20),@lDbName varchar(50),@SQLQuery varchar(max), @ErpId int&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; select @lServerIp = Server_IP,@lDbName = Db_name,@ErpId=ERP_ID from TBL_MAP_COMPANY_SERVER&nbsp; where Company_Id = @pCompany_id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; set @lFinalString = '[' + @lServerIp +'].['+ @lDbName +'].dbo.'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;CREATE TABLE #TEMP&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;(PERIOD_IN_WORD VARCHAR(20), PRDENDDATE VARCHAR(20), PERIOD_ENDDATE VARCHAR(50))&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; if(@ErpId=1)&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; begin&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; SET @SQLQuery = 'Select&nbsp; CASE WHEN SUBNUM=1 THEN ''One''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=2 THEN ''Two''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=3 THEN ''Three''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;WHEN SUBNUM=4 THEN ''Four''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=5 THEN ''Five''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=6 THEN ''Six''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=7 THEN ''Seven''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=8 THEN ''Eight''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=9 THEN ''Nine''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=10 THEN ''Ten''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=11 THEN ''Eleven''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN SUBNUM=12 THEN ''Twelve''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;END,CONVERT(VARCHAR,CAST(CAST(T_RefDate AS VARCHAR)AS DATE) , 106),format(T_RefDate,''MMMM dd, yyyy'') From ' + @lFinalString&nbsp; + 'OFPR where (Code) = '''+@periodRange+''''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;End&nbsp; &nbsp;&nbsp;else if(@ErpId=2)&nbsp; &nbsp;&nbsp;begin&nbsp; &nbsp;&nbsp;&nbsp;SET @SQLQuery = 'SELECT&nbsp; &nbsp;&nbsp;DISTINCT&nbsp; &nbsp; &nbsp;CASE WHEN CA.PERIOD=1 THEN ''One''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=2 THEN ''Two''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=3 THEN ''Three''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=4 THEN ''Four''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=5 THEN ''Five''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=6 THEN ''Six''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=7 THEN ''Seven''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=8 THEN ''Eight''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=9 THEN ''Nine''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=10 THEN ''Ten''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=11 THEN ''Eleven''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;WHEN CA.PERIOD=12 THEN ''Twelve''&nbsp; &nbsp; &nbsp; WHEN CA.PERIOD=13 THEN ''Thirteen''&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;END AS [PERIOD_IN_WORD]&nbsp; &nbsp;&nbsp;,&nbsp; &nbsp;&nbsp;CONVERT(VARCHAR,CAST(CAST( CA.ENDDATE AS VARCHAR)AS DATE) , 106) AS [PERIODENDDATE]&nbsp; ,&nbsp;&nbsp;format(cast(cast(CA.ENDDATE as varchar)as date),''MMMM dd, yyyy'') AS [PERIOD_ENDDATE]&nbsp;&nbsp;FROM ' + @lFinalString&nbsp; + 'CSFSC&nbsp; &nbsp; &nbsp;CROSS APPLY&nbsp; &nbsp;&nbsp;(&nbsp; &nbsp;&nbsp;VALUES&nbsp; &nbsp;&nbsp;(1,BGNDATE1,ENDDATE1),&nbsp; &nbsp;&nbsp;(2,BGNDATE2,ENDDATE2),&nbsp; &nbsp;&nbsp;(3,BGNDATE3,ENDDATE3),&nbsp; &nbsp;&nbsp;(4,BGNDATE4,ENDDATE4),&nbsp; &nbsp;&nbsp;(5,BGNDATE5,ENDDATE5),&nbsp; &nbsp;&nbsp;(6,BGNDATE6,ENDDATE6),&nbsp; &nbsp;&nbsp;(7,BGNDATE7,ENDDATE7),&nbsp; &nbsp;&nbsp;(8,BGNDATE8,ENDDATE8),&nbsp; &nbsp;&nbsp;(9,BGNDATE9,ENDDATE9),&nbsp; &nbsp;&nbsp;(10,BGNDATE10,ENDDATE10),&nbsp; &nbsp;&nbsp;(11,BGNDATE11,ENDDATE11),&nbsp; &nbsp;&nbsp;(12,BGNDATE12,ENDDATE12),(13,BGNDATE13,ENDDATE13)&nbsp;) AS CA (PERIOD,STARTDATE,ENDDATE)&nbsp; &nbsp;&nbsp;WHERE CSFSC.ACTIVE=1 and (''FY''+CONVERT(VARCHAR(4),CSFSC.FSCYEAR)+''-''+CONVERT(VARCHAR(2),FORMAT(CA.PERIOD,''00'')) ) = '''+@periodRange+''''&nbsp; &nbsp; &nbsp; &nbsp;end&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;INSERT INTO #TEMP EXEC (@SQLQuery)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;SELECT * FROM #TEMP&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; END&nbsp;&nbsp;&nbsp;&nbsp;GO&nbsp;&nbsp;

lbendlin · Answer

That link is irrelevant and/or misleading.&nbsp;&nbsp;
&nbsp;
Power Query and SQL Server Stored Procedures - BI Insight

Forum Discussion

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

5 Replies

Related content

Execute Stored Procedure

Cannot execute a stored procedure

Unable to execute Stored Procedure in Lakehouse using Data Pipeline

Transport Level Error on Stored Procedure Execution in Fabric Warehouse

Stored Procedure to Manipulate Tables