SELECT Item.Id
	FROM Item 
	WHERE
	ROW_NUMBER() OVER   
	(    
		ORDER BY SKU
		/* same expression as in the ORDER BY of the whole query */  
	) 
	BETWEEN (@Page - 1) * @PageSize + 1 AND (@Page * @PageSize)  
	 /* AND more conditions ... */
	ORDER BY  
		CASE WHEN @OrderByDirection = 'A' THEN    
			CASE @OrderBy       
			WHEN 'SKU'    THEN Item.SKU      
			WHEN 'Description' THEN Item.Description       
			END  
		END,  
		CASE WHEN @OrderByDirection = 'D' THEN    
			CASE @OrderBy       
			WHEN 'SKU'    THEN Item.SKU      
			WHEN 'Description' THEN Item.Description      
			END   
	END DESC

WITH ItemList AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY Item.SKU) AS RowNum, Item.Id
    FROM Item left join (SELECT Cost.Id 
						 FROM Cost, VendorFactory	
				         WHERE Cost.VendorFactoryId = VendorFactory.Id) cost
	ON Item.CostId = cost.Id

	WHERE (isnull(@SKU,'') = '' or (Item.SKU like @SKU))
	and (isnull(@Description,'') = '' or (Item.Description like @Description))
	and Item.StatusId in (SELECT ChangeType
						  FROM  dbo.SplitChangeTypes(@StatusIdList))
)

	SELECT *
	FROM ItemList
	WHERE RowNum BETWEEN (@Page - 1) * @PageSize + 1 AND @Page * @PageSize
	ORDER BY  
		CASE WHEN @OrderByDirection = 'A' THEN    
			CASE @OrderBy       
			WHEN 'SKU'    THEN Item.SKU      
			WHEN 'Description' THEN Item.Description      
			/* more... */    
			END  
		END,  

		CASE WHEN @OrderByDirection = 'D' THEN    
			CASE @OrderBy       
			WHEN 'SKU'    THEN Item.SKU      
			WHEN 'Description' THEN Item.Description      /* more... */    
			END   
		END DESC

WHEN @OrderByDirection = 'A' AND @OrderBy = 'Vendor' THEN 
ROW_NUMBER() OVER(ORDER BY VendorFactory.Vendor) 

quote:

---

Originally posted by ajcool123

yeah that does but i keep getting this error.
thoughts?


Msg 4104, Level 16, State 1, Procedure uspItem_RtrvItemForSKU, Line 25
The multi-part identifier "VendorFactory.Id" could not be bound.

---

;WITH ItemList AS
(
SELECT 
CASE WHEN @OrderByDirection = 'A' AND @OrderBy = 'SKU' THEN ROW_NUMBER() OVER(ORDER BY Item.SKU) 
WHEN @OrderByDirection = 'A' AND @OrderBy = 'SKU' THEN ROW_NUMBER() OVER(ORDER BY Item.Description)
-- Add more order by conditions here if needed
END AS RowNum
, Item.Id
, costVendorFactory.Id
-- add more fields here if needed
FROM Item 
LEFT JOIN (SELECT Cost.Id 
			FROM Cost, VendorFactory 
			WHERE Cost.VendorFactoryId = VendorFactory.Id) cost
ON Item.CostId = cost.Id

WHERE (isnull(@SKU,'') = '' or (Item.SKU like @SKU))
AND (isnull(@Description,'') = '' or (Item.Description like @Description))
AND Item.StatusId in (SELECT ChangeType
FROM dbo.SplitChangeTypes(@StatusIdList))
)

SELECT *
FROM ItemList
WHERE RowNum BETWEEN (@Page - 1) * @PageSize + 1 AND @Page * @PageSize