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

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