Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

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 ...
Need Help
lbendlin's avatar
lbendlin
Super User
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

      what's the purpose of 

       

      SELECT * FROM OPENROWSET

       ?  

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago
        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
         
        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