CREATE PROCEDURE Items_Search
	@PageSize		INT,
	@PageIndex		INT,
	@ID			INT,
	@ItemCode		VARCHAR(18),
	@ItemDesc		VARCHAR(80),
	@ItemCategory		TINYINT,
	@Total			INT OUTPUT	
AS
BEGIN

	WITH ITEMSEARCH AS
	(
		SELECT ID, ItemCode, ItemDesc, ItemCategory, Image150x150, Image250x250, Image500x500,
				ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
				FROM ITEMS
				WHERE (...)
	)
	
	SELECT * FROM ITEMSEARCH WHERE (RowNumber > ((@PageSize * @PageIndex) - @PageSize) AND RowNumber <= (@PageSize *  
                                        @PageIndex))
		
	
END

Here my Requirement is to Get the Total Row Count which is got as a result of implementing 'WHERE' condition,

the where condition tend to grow as more when more search criteria is added, in that case executing select statement
with the WHERE condition for more than once to get the Total Count and RecordSet will not be good

And only one SELECT statement can follow a WITH Class.

So as per this condition I need help to

Execute the select statement with WHERE clause only once from that i need TOTAL as well as the 
RecordSet of the SELECT Statement based on Row_Number() Condition.


Please raise question if i'm not clear with my problem, so that i'll try to explain it better

Thanks

SELECT *, (select count(*) from ItemSearch)
FROM ITEMSEARCH WHERE (RowNumber > ((@PageSize * @PageIndex) - @PageSize) AND RowNumber <= (@PageSize * @PageIndex))

quote:

---

Originally posted by prakashdotc

Hi nathans,

Thank U for your solution, it's working fine.

I need another help.

In the Same SP which is specified in the previous post,
i'll pass 'SortBy' as another parameter in that i'll pass values as 0, 1, 2 etc

based on this SortBy Value i have to include ORDER BY to the 'SELECT' query which comes after the 'WITH' class

how to do that....?

Thanks in Advance

---