May not be what you're after, but you can return an additional column with the total row count

Alter proc [dbo].[InvoiceDisplayTest] (@InvoiceNumber nvarchar(50),@InvoiceDate datetime ,@InvoiceDate1 datetime, @AccountNumber nvarchar(50),@TradingPartnerID nvarchar(50),@Page nvarchar(50),@rownum int OUTPUT)

AS

BEGIN

SELECT * FROM (SELECT InvoiceID,DocumentNumber, convert(char(12),DocumentDate,112) AS DocumentDate,AccountNumber,Supplier,TradingPartnerID,

ROW_NUMBER()OVER(ORDER BY DocumentNumber ASC)AS rowno,

COUNT(*) OVER() AS TotalRows

FROM InvoiceHeader

WHERE (@InvoiceNumber = '' OR DocumentNumber = @InvoiceNumber)

and

(@AccountNumber = '' or AccountNumber = @AccountNumber)

and

(@TradingPartnerID = '' or TradingPartnerID = @TradingPartnerID)

and

(@InvoiceDate = '' or DocumentDate >= @InvoiceDate

and

DocumentDate <=@InvoiceDate1 or @InvoiceDate1 = '')

)As qr

WHERE rowno BETWEEN ((@Page - 1) * 20 + 1)

AND (@Page * 20)

END

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537