Join CTE and regular table throwing error invalid object

Question

This is my full SP code

/*  
<?xml version="1.0" encoding="utf-16"?>  
<Root>  
  <TickerTemplate>  
    <LineItem>Total Revenue - Proforma</LineItem>  
    <XFundCode>ASREP821430</XFundCode>  
    <IsRemoved>false</IsRemoved>  
  </TickerTemplate>  
  <TickerTemplate>  
    <LineItem>Total Revenue - GAAP</LineItem>  
    <XFundCode>TRIN</XFundCode>  
    <IsRemoved>false</IsRemoved>  
  </TickerTemplate>  
</Root>  
*/  
ALTER PROCEDURE [dbo].[USP_BulkUpdateXFundCode]       
@TickerID VARCHAR(20),        
@XmlData XML  

AS        
BEGIN    
 CREATE TABLE #Temp    
 (    
  LineItem VARCHAR(MAX)    
 )   

 ;WITH CTEXml (LineItem, XFundCode) as (  
  SELECT d.v.value('(LineItem/text())[1]','VARCHAR(MAX)') AS LineItem,  
  d.v.value('(XFundCode/text())[1]','VARCHAR(MAX)') AS XFundCode                    
  FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)    
 ),  
 CTEJoinData (ID, LineItem, XFundCode) as (  
  SELECT b.ID,b.LineItem,a.XFundCode FROM CTEXml a   
  INNER JOIN TblLineItemTemplate b on a.LineItem=b.LineItem   
 )  

 INSERT INTO #Temp (LineItem)    
 SELECT * FROM    
 (    
  SELECT UPPER(TRIM(d.v.value('(LineItem/text())[1]','VARCHAR(MAX)'))) AS LineItem                   
  FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)    
  EXCEPT    
  SELECT UPPER(TRIM(LineItem)) AS LineItem FROM TblLineItemTemplate WHERE TickerID = @TickerID    
 ) X    


 --Update TblLineItemTemplate   
 --Set XFundCode = a.XFundCode  
 --From TblLineItemTemplate l INNER JOIN CTEJoinData a ON l.LineItem=a.LineItem  
 --WHERE l.TickerID=@TickerID  

 select a.XFundCode from CTEJoinData a 
 inner join TblLineItemTemplate l on a.LineItem = l.LineItem

 SELECT * FROM #Temp  
 DROP TABLE #Temp  


END

1) for a.XFundCode sql server saying multi part identifier. could not be bound
2) saying invalid object name CTEJoinData

where i made the mistake in my code ?

please help me.

Answer 1

You are treating the CTE as if it like a temp table or table variable. It isn't. The CTE is only available directly following the definition. It isn't an object that exists for later in your code module. If you need that functionality, use a table variable or (preferably if you aren't on 2019) a temp table

Answer 2

Hi @Sudip Bhatt ,

Actually, it is not possible to get multiple result sets from a single CTE.

You could however use a table variable or a temp table to cache some of the information and use it later instead of issuing the same complex query multiple times.

Please refer below query as an example:

 ;with cte as   
    (select 1 col1,2 col2)  
      
    select * from cte  
      
    select * from cte  

After running above query, you would receive the error 'Invalid object name 'cte''.

Please refer below query and check whether it is helpful.

/*    
 <?xml version="1.0" encoding="utf-16"?>    
 <Root>    
   <TickerTemplate>    
     <LineItem>Total Revenue - Proforma</LineItem>    
     <XFundCode>ASREP821430</XFundCode>    
     <IsRemoved>false</IsRemoved>    
   </TickerTemplate>    
   <TickerTemplate>    
     <LineItem>Total Revenue - GAAP</LineItem>    
     <XFundCode>TRIN</XFundCode>    
     <IsRemoved>false</IsRemoved>    
   </TickerTemplate>    
 </Root>    
 */    
 ALTER PROCEDURE [dbo].[USP_BulkUpdateXFundCode]         
 @TickerID VARCHAR(20),          
 @XmlData XML    
              
 AS          
 BEGIN      
  CREATE TABLE #Temp      
  (      
   LineItem VARCHAR(MAX)      
  )     
        
  ;WITH CTEXml (LineItem, XFundCode) as (    
   SELECT d.v.value('(LineItem/text())[1]','VARCHAR(MAX)') AS LineItem,    
   d.v.value('(XFundCode/text())[1]','VARCHAR(MAX)') AS XFundCode                      
   FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)      
  )  
    
  SELECT b.ID,b.LineItem,a.XFundCode   
   INTO #Temp1  
   FROM CTEXml a     
   INNER JOIN TblLineItemTemplate b on a.LineItem=b.LineItem    
        
  INSERT INTO #Temp (LineItem)      
  SELECT * FROM      
  (      
   SELECT UPPER(TRIM(d.v.value('(LineItem/text())[1]','VARCHAR(MAX)'))) AS LineItem                     
   FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)      
   EXCEPT      
   SELECT UPPER(TRIM(LineItem)) AS LineItem FROM TblLineItemTemplate WHERE TickerID = @TickerID      
  ) X      
        
       
  --Update TblLineItemTemplate     
  --Set XFundCode = a.XFundCode    
  --From TblLineItemTemplate l INNER JOIN #Temp1 a ON l.LineItem=a.LineItem    
  --WHERE l.TickerID=@TickerID    
       
  select a.XFundCode from #Temp1 a   
  inner join TblLineItemTemplate l on a.LineItem = l.LineItem  
      
  SELECT * FROM #Temp    
  DROP TABLE #Temp    
  DROP TABLE #Temp1    
        
        
 END  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.