mardi 22 septembre 2015

How to do the pagination in sql server 2005 when multiple table used by joining

i know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. so i need suggestion like how to use pagination there ?

i used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.

 IF IsNull(@GroupID,'') = ''      
  SELECT  IsNull(PartGroupName, 'UnMapped') AS PartGroupName,      
     CASE IsNull(PartGroupName, '')      
      WHEN '' THEN ''       
      ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')      
     END AS PartGroupID,      
     IsNull(A.OEReference, B.OEReference) AS OEReference,   
     IsNull(SaleDone,0) AS [SALE/OUR],        
     IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]      
  FROM  @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference      
     LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code      
     LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0  
  ORDER BY IsNull(PartGroupName, 'UnMapped')   
 ELSE      
  SELECT  IsNull(PartGroupName, 'UnMapped') AS PartGroupName,      
     CASE IsNull(PartGroupName, '')      
      WHEN '' THEN ''       
      ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'')      
     END AS PartGroupID,      
     IsNull(A.OEReference, B.OEReference) AS OEReference,   
     IsNull(SaleDone,0) AS [SALE/OUR],        
     IsNull(WarrantyDone, 0) AS [WARRANTY/WARRANTYOUR]

  FROM  @Sale A FULL OUTER JOIN @Warranty B ON A.OEReference = B.OEReference      
     LEFT OUTER JOIN BBAPart K ON IsNull(A.OEReference,B.OEReference) = K.Stock_code      
     LEFT OUTER JOIN BBAPartGroup C ON K.GroupID=C.PartGroupID AND C.IsDeleted = 0      
  WHERE  PartGroupID IN (SELECT Data FROM dbo.stringtotable(@GroupID, ',') )      
  ORDER BY IsNull(PartGroupName, 'UnMapped')   
END   

Aucun commentaire:

Enregistrer un commentaire