Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"

Question

i have one dynamic sql statement when i am executing that statement then i am getting the error Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"

i use COLLATE SQL_Latin1_General_CP1_CI_AS or COLLATE DATABASE_DEFAULT but still got the error

here is my sql snap

ALTER PROC SearchByJID  
(  
 @JID    VARCHAR(max),  
 @TrackNo   VARCHAR(max),  
 @PageSize        INT,                      
 @PageNumber      INT    
)  
AS  

DECLARE @SQL VARCHAR(MAX)         
DECLARE @Count_SQL VARCHAR(MAX)                    
DECLARE @RowStart INT                      
DECLARE @RowEnd INT                      

SET @PageNumber = @PageNumber - 1                      
SET @RowStart = @PageSize * @PageNumber + 1                      
SET @RowEnd = @RowStart + @PageSize - 1                      
SET @SQL=''          
SET @Count_SQL=''                   


 BEGIN        
   SET @SQL='SELECT * FROM  (SELECT Row_number() OVER (ORDER BY [bbajobs].JID ) AS RowNumber,                      
   [BBAJobs].[JID],[AccountReference] as [Acc Ref],[BBAJobs].[OEReference] as [OERef],[JobType],[JobState],  
   [JobShippedDate],[UPSShippingNumber],[CustomerName] [Customer Name],[ContactName] [Contact Name],[Telephone],  
   [JobDescription],[CallRem].[rem],[CallRem].[callStatus],[CallRem].[ShopRemarks],  
   CustomerNotes,ShopNotes,RecievedDate,UserName FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem   
   ON dbo.BBAJobs.JID = dbo.CallRem.jid WHERE 1<2 '  

   IF @TrackNo <> ''  
   BEGIN                    
    SET @SQL=@SQL+' AND [bbajobs].jid IN (select jid from trackdetail WHERE TRACKNO IN(  
    SELECT CONVERT(VARCHAR, data) AS [JID]                     
    FROM dbo.Split1('''+@TrackNo+''', '','')))'        
   END  

   IF ltrim(rtrim(@JID)) <> ''                    
   BEGIN                    
    SET @SQL=@SQL+' AND [bbajobs].jid IN (SELECT CONVERT(VARCHAR, data) AS [JID]                     
    FROM dbo.Split1('''+@JID+''', '','')) '        
   END  

   SET @SQL=@SQL+') x WHERE RowNumber BETWEEN '+CAST(@RowStart AS VARCHAR)+' AND '+CAST(@RowEnd AS VARCHAR)              

-- Get Count  
   SET @Count_SQL= 'SELECT COUNT(bbajobs.[JID]) FROM dbo.BBAJobs LEFT OUTER JOIN dbo.CallRem   
   ON dbo.BBAJobs.JID = dbo.CallRem.jid WHERE 1<2 '  

   IF @TrackNo <> ''  
   BEGIN                    
    SET @Count_SQL=@Count_SQL+' AND bbajobs.jid IN (select jid from trackdetail WHERE TRACKNO IN(  
    SELECT CONVERT(VARCHAR, data) AS [JID]                     
    FROM dbo.Split1('''+@TrackNo+''', '','')))'        
   END  

   IF ltrim(rtrim(@JID)) <> ''                    
   BEGIN                    
    SET @Count_SQL=@Count_SQL+' AND bbajobs.jid IN (SELECT CONVERT(VARCHAR, data) AS [JID]                     
    FROM dbo.Split1('''+@JID+''', '',''))'                     
   END  
 END  

exec(@SQL+@Count_SQL)  

specially i am getting the error when i am sending track number so i guess here is error lies

IF @TrackNo <> ''  
   BEGIN                    
    SET @SQL=@SQL+' AND [bbajobs].jid IN (select jid from trackdetail WHERE TRACKNO IN(  
    SELECT CONVERT(VARCHAR, data) AS [JID]                     
    FROM dbo.Split1('''+@TrackNo+''', '','')))'        
   END  

so guide me what to change in sql to fix this problem.

i did this way & solved the problem

   IF @TrackNo <> ''  
   BEGIN                    
    SET @SQL=@SQL+' AND [bbajobs].jid IN (select jid from trackdetail 
    WHERE TRACKNO COLLATE DATABASE_DEFAULT IN(  
    SELECT data COLLATE DATABASE_DEFAULT AS [JID]                      
    FROM dbo.Split1('''+@TrackNo+''', '','')))'       
   END  

Answer 1

Try

SET @SQL=@SQL+' AND [bbajobs].jid IN (select jid from trackdetail WHERE 
TRACKNO collate database_default IN(  
SELECT CONVERT(VARCHAR, data) AS [JID] collate database_default                     
FROM dbo.Split1('''+@TrackNo+''', '','')))'