SQL Server: Store procedure call return error All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

T.Zacks 3,986 Reputation points
2021-12-01T17:24:48.36+00:00

I am calling a dynamic sql from a SP which return a error called All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

from this error it is clear that fields mismatch in select list of union.

my full SP code is big so i am not giving complete code. I am giving only the snippet from where error is generating. in the below SP there are few union and i saw number of fields are same in all select of union. if anyone see any mistake then please point out the area. thanks

SET @sql = '                                                                                         
 Select '''+ @InHouseCode +''' AS InHouseCode,'''+ISNULL(@ComapnyNameColor,'') +''' AS ComapnyNameColor, '''+  @ClientName +''' AS ClientName,'''+CAST(@NewEarnings AS VARCHAR)+''' AS EarningID, XX.*,'''' scale/*,Isnull(ZZ.AllowComma,''FALSE'') AllowComma
,Isnull(ZZ.AllowedDecimalPlace,''0'') AllowedDecimalPlace             
,                                                                                                    
 Isnull(ZZ.AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(ZZ.CurrencySign,'''') CurrencySign*/,Isnull(BM_Denominator,'''') BM_Denominator,                                            
(                                            
 SELECT TOP 1 Isnull(cf.AllowPercentageSign,''FALSE'')                                             
 FROM tblTicker_LiConfig cf                           
 INNER JOIN tblSectionTemplate s ON cf.SectionID=s.SectionID                                            
 INNER JOIN TblLineItemTemplate l ON cf.LineItemID=l.ID                                            
 WHERE cf.TickerID='''+@TickerID+''' AND s.TickerID='''+@TickerID+''' AND l.TickerID='''+@TickerID+'''                                            
 AND s.Section=XX.Section AND l.LineItem=XX.LineItem                                            
) AllowPercentageSign,                                            
(                                            
 SELECT TOP 1 Isnull(cf.AllowComma,''FALSE'')                                             
 FROM tblTicker_LiConfig cf                                            
 INNER JOIN tblSectionTemplate s ON cf.SectionID=s.SectionID                                            
 INNER JOIN TblLineItemTemplate l ON cf.LineItemID=l.ID                                            
 WHERE cf.TickerID='''+@TickerID+''' AND s.TickerID='''+@TickerID+''' AND l.TickerID='''+@TickerID+'''                                            
 AND s.Section=XX.Section AND l.LineItem=XX.LineItem                                            
) AllowComma,                                            
(                                            
 SELECT TOP 1 Isnull(cf.AllowedDecimalPlace,''0'')                         
 FROM tblTicker_LiConfig cf                                            
 INNER JOIN tblSectionTemplate s ON cf.SectionID=s.SectionID                                
 INNER JOIN TblLineItemTemplate l ON cf.LineItemID=l.ID                                            
 WHERE cf.TickerID='''+@TickerID+''' AND s.TickerID='''+@TickerID+''' AND l.TickerID='''+@TickerID+'''                                            
 AND s.Section=XX.Section AND l.LineItem=XX.LineItem                                            
) AllowedDecimalPlace,                                            
(                                            
 SELECT TOP 1 Isnull(cf.CurrencySign,'''')                                             
 FROM tblTicker_LiConfig cf                                            
 INNER JOIN tblSectionTemplate s ON cf.SectionID=s.SectionID                                            
 INNER JOIN TblLineItemTemplate l ON cf.LineItemID=l.ID                                            
 WHERE cf.TickerID='''+@TickerID+''' AND s.TickerID='''+@TickerID+''' AND l.TickerID='''+@TickerID+'''                                            
 AND s.Section=XX.Section AND l.LineItem=XX.LineItem                                            
) CurrencySign                  

 From                                                                                                     
 (                                                                                          
  -- Displaying GROUP                                                        
 Select AA.Section,  
 AA.LineItem,  
 Csm.DisplayInCSM,   
 IIF(ISNULL(Csm.IsExpense,''N'')=''N'',''N'',''Y'') AS IsExpense,  
 AA.BrokerCode Broker,  
 AA.BrokerName,  
 '''' BM_Element,  
 '''' BM_Code,  
 AA.Ord,  
 AA.[Revise Date],  
 AA.LineItemId,                                         
 Csm.ID,  
 ParentID,  
 [FontName],  
 [FontStyle],  
 [FontSize],  
 [UnderLine],  
 [BGColor],  
 [FGColor],  
 [Indent],  
 [Box],  
 [HeadingSubHeading],                                                                                                    
 '+@PeriodCols+','+@PeriodColsComment +  
 ',LineItem_Comment,  
 BrokerName_Comment,  
 Date_Comment,  
 ''Group'' [RowType],  
 '''' AS CalculationMethod,                                                          
 '''' AS BlueMatrix1stElementFormula,  
 '''' AS ParentGroupName           
 From tblCSM_ModelDetails Csm LEFT OUTER JOIN  (                                                                                                    
  Select b.*,L.ID LineItemId                                                                                                       
  From #TmpAll_Broker_LI b                                                                                                    
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem                                                                                                    
     ) AA ON Csm.LineItemId=AA.LineItemId                                                                                                    
 WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Type =''GROUP''                                                                                               

 UNION                                                                                              

 ----Displaying Broker Detail                                                                                                    
 Select AA.Section,  
 AA.LineItem,  
 Csm.DisplayInCSM,  
 IIF(ISNULL(Csm.IsExpense,''N'')=''N'',''N'',''Y'') AS IsExpense,  
 AA.BrokerCode Broker,  
 AA.BrokerName,  
 '''' BM_Element,  
 '''' BM_Code,  
 AA.Ord,  
 AA.[Revise Date],  
 AA.LineItemId,                                          
 Csm.ID,  
 ParentID,  
 [FontName],  
 [FontStyle],  
 [FontSize],  
 [UnderLine],  
 [BGColor],  
 [FGColor],  
 [Indent],  
 [Box],  
 [HeadingSubHeading],                                                    
 '+@PeriodCols+','+@PeriodColsComment   
 +',LineItem_Comment,  
 BrokerName_Comment,  
 Date_Comment,  
 ''LineItem'' [RowType],  
 '''' AS CalculationMethod,                                                          
 '''' AS BlueMatrix1stElementFormula,                                                  
 (                  
 SELECT DisplayInCSM FROM  tblCSM_ModelDetails WHERE ID=Csm.ParentID AND CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+'                                                  
 ) AS ParentGroupName                                                  
 From tblCSM_ModelDetails Csm LEFT OUTER JOIN  (                                                                                                    
  Select b.*,L.ID LineItemId                                                                                                       
  From #TmpAll_Broker_LI b                                                                                                    
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem                                                                                       
     ) AA ON Csm.LineItemId=AA.LineItemId                                                                                                    
 WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0 AND Type =''LINEITEM''                                                
 AND Csm.LineItemID IN (SELECT b.ID FROM #TmpAll_Broker_LI a INNER JOIN TblLineItemTemplate b ON a.LineItem=b.LineItem)                                                                                            


 UNION                                                                                                     
 ----- Displaying Consensus                                                                                                    
 Select Section,   
 b.LineItem,  
 DisplayInCSM,   
 IIF(ISNULL(Csm.IsExpense,''N'')=''N'',''N'',''Y'') AS IsExpense,  
 '''' Broker,  
 '''' BrokerName,  
 '''' BM_Element,  
 '''' BM_Code,   
 Ord,  
 '''' [Revise Date],  
 L.ID LineItemID,                                                      
   Csm.ID,  
   ParentID,  
   [FontName],  
   [FontStyle],  
   [FontSize],  
   [UnderLine],  
   [BGColor],  
   [FGColor],  
   [Indent],  
   [Box],  
   [HeadingSubHeading],                                                          
  '+@PeriodCols+','+@PeriodColsComment   
  +',LineItem_Comment,  
  BrokerName_Comment,  
  Date_Comment,  
  ''Consensus'' [RowType],                                            
  (                                                              
  Select   CASE                                                               
  WHEN cd.CalculationMethod IS NULL THEN ''A''          
  WHEN cd.CalculationMethod=''Average'' THEN ''A''                                                              
  WHEN cd.CalculationMethod=''Median'' THEN ''M''                                                              
   END AS [CalculationMethod]                                                              
   FROM tblCSM_ModelDetails cd WHERE cd.ID=csm.ParentID and cd.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))                                                              
 +') AS CalculationMethod,  
 '''' AS BlueMatrix1stElementFormula,  
 '''' AS ParentGroupName                                                              
  From #TmpZacksCons b                                                                                                    
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem                                                                                                    
  INNER JOIN tblCSM_ModelDetails Csm ON Csm.LineItemID=L.ID                                                                                                      
  WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0                                                                                                    


  UNION                                                                               
  --Displaying Blue Metrics                                                    

  Select Section,   
  b.LineItem,  
  DisplayInCSM,  
  IIF(ISNULL(Csm.IsExpense,''N'')=''N'',''N'',''Y'') AS IsExpense,  
  '''' Broker,  
  '''' BrokerName,  
  BM_Element,   
  IIF(LEN(Code)=1,''0''+Code,Code) AS BM_Code,   
  Ord,  
  '''' [Revise Date],  
  L.ID LineItemID,                           
   Csm.ID,  
   ParentID,  
   [FontName],  
   [FontStyle],  
   [FontSize],  
   [UnderLine],  
   [BGColor],  
   [FGColor],  
   [Indent],  
   [Box],  
   [HeadingSubHeading],                                                                           
   '+@PeriodCols+','+@PeriodColsComment   
   +',LineItem_Comment,  
   BrokerName_Comment,  
   Date_Comment,  
   ''BM'' [RowType],                                                              
   '''' AS CalculationMethod,                                                          
   (                                                          
    SELECT CASE                                      
    WHEN IIF(LEN(Csm.BMID)=1,''0''+Csm.BMID,Csm.BMID) =''01'' THEN                                                          
    (                                                          
    SELECT top 1 dbo.fn_TranslateFormulaToText('''+@TickerID+''',c.BlueMatrix1stElementFormula,''N'')                                                           
    FROM  tblTicker_LiConfig c  WHERE c.TickerID='''+@TickerID+''' and c.BlueMatrix1stElementFormula<>''''                                                           
    AND c.LineItemID=L.ID                                                           
    )                                                           
    ELSE ''''                                                          
    END                                                          
   ) AS BlueMatrix1stElementFormula,  
   '''' AS ParentGroupName                                                          
  From #TmpBM b                                                                                                    
  INNER JOIN TblLineItemTemplate L ON TickerID='''+@TickerID+''' AND b.LineItem= L.LineItem                                                           
  INNER JOIN tblCSM_ModelDetails Csm ON Csm.BMID=b.code AND Csm.LineItemID=L.ID                                                                        
  WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+'                                                    
  AND Ord IS NOT NULL                                                                                                    
 ) XX                                                                                               
 Left Outer Join tblLiConfig ZZ                                                                                                
  On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''                                             
 Order by ID,Ord,BM_Code,LineItem,BrokerName'
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.9K Reputation points MVP
    2021-12-01T22:40:17.457+00:00

    So instead of you counting the number of columns,, you hope that someone else will find it amusing to do so?

    Nope, sorry. What we can do is to learn you to work with dynamic SQL. Very important is to use debug PRINTs. Now, since that is long, a regular PRINT may be truncated, but look here for alternatives: https://www.sommarskog.se/dynamic_sql.html#debugprint

    And stop inlining your parameters like this:

    On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker='''+@Ticker+'''

    That should be 

       On XX.Section=ZZ.Section And XX.LineItem=ZZ.LI And ZZ.Ticker= @Ticker

    Then @Ticker, @Craig _id etc should be passed as parameters to the dynamic SQL. I believe I have told you this more one time. Why do you keep on making things difficult for yourself?

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-12-02T02:26:08.003+00:00

    Hi @T.Zacks ，

    As everyone said, you can use PRINT to output your error statements and troubleshoot problems. And when your dynamic SQL encounters problems in the future, this should become the method you often use to find the problem.
    Since we don't have all the variables involved in your code, we can't output the statement and find the problem. Because of the splicing of many variables in dynamic SQL statements, if we do not PRINT, we cannot directly see whether the number of columns before and after UNION is the same.

    Regards,
    Echo

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-12-01T18:28:13.88+00:00

    Print your dynamic SQL statement and run the individual queries without the UNION to see what is returned.

    0 comments