Incorrect syntax near the keyword WHERE

Question

I have long dynamic store procedure where i add a order by clause in inner query and now i am getting this error. Incorrect syntax near the keyword 'WHERE'.

This line i added

WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+' AND Csm.BMID=0 AND Type !=''SHEET''
ORDER BY AA.BrokerName

after adding this order by ORDER BY AA.BrokerName i am getting error when executing my SP.

Here i am pasting a chunk of the SP. so anyone can tell me where to order BrokerName field

 SET @sql = '        
 Select XX.*,'''' scale,Isnull(AllowComma,''FALSE'') AllowComma,Isnull(AllowedDecimalPlace,''0'') AllowedDecimalPlace,        
     Isnull(AllowPercentageSign,''FALSE'') AllowPercentageSign,Isnull(CurrencySign,'''') CurrencySign,Isnull(BM_Denominator,'''') BM_Denominator        
 From         
 (        
 ---- Broker Detail        
 Select TOP 99.999999 PERCENT AA.Section,AA.LineItem,Csm.DisplayInCSM ,AA.BrokerCode Broker,AA.BrokerName,'''' BM_Element,'''' BM_Code,AA.Ord,AA.[Revise Date],AA.LineItemId,        
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],        
 '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment        
 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 UPPER(TRIM(b.LineItem))= UPPER(TRIM(L.LineItem))  
  ) AA ON UPPER(TRIM(Csm.LineItemId)=UPPER(TRIM(AA.LineItemId))  
 WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@CSM_Id))+' AND Csm.BMID=0 AND Type !=''SHEET''    
 ORDER BY AA.BrokerName  
 UNION         
 ----- Consensus        
 Select Section, b.LineItem,DisplayInCSM, '''' Broker,'''' BrokerName,'''' BM_Element,'''' BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,        
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],        
  '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment        
  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        
 ---- Blue Metrics        
     UNION        
  Select Section, b.LineItem,DisplayInCSM,'''' Broker,'''' BrokerName,BM_Element,Code BM_Code, Ord,'''' [Revise Date],L.ID LineItemID,        
   Csm.ID,[FontName],[FontStyle],[FontSize],[UnderLine],[BGColor],[FGColor],[Indent],[Box],[HeadingSubHeading],        
   '+@PeriodCols+','+@PeriodColsComment +',LineItem_Comment,BrokerName_Comment,Date_Comment        
  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'    

if i add BrokerName field at the last order by then broker name is not coming alphabetical order wise. Please suggest how to get rid of runtime error called Incorrect syntax near the keyword 'WHERE'.

Thanks

Answer 1

WHERE Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+' AND Csm.BMID=0 AND Type !=''SHEET''

ORDER BY AA.BrokerName

This should be:

WHERE Csm.CSM_ID= @CSM_Id AND Csm.BMID=0 AND Type !=''SHEET''  
ORDER BY AA.BrokerName  

And @Craig _Id should be a parameter to your dynamic SQL:

EXEC sp_executsql @sql, N'@CSM_id int', @CSM_id  

There are many reasons why you should inline parameters to your SQL strings. One reason is that this is lot more difficult than just passing parameters.

Answer 2

Hi @Sudip Bhatt ,

I think you are missing quotation mark around the CSM_ID. Please try Csm.CSM_ID='''+TRIM(CONVERT(CHAR(10),@Craig _Id))+''' instead of Csm.CSM_ID='+TRIM(CONVERT(CHAR(10),@Craig _Id))+'

Hope this will help you. Thanks!