InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 18:09:58 GMT20http://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxYou might also want to have a read through this: [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]Thu, 13 Sep 2012 03:22:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxNope thats absolutely fine. I just wanted a way to return the total number of rows to php for pagination. Perfect thanks very muchThu, 13 Sep 2012 02:58:30 GMTmark.dungey 56406http://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxMay not be what you're after, but you can return an additional column with the total row count[code="sql"]Alter proc [dbo].[InvoiceDisplayTest] (@InvoiceNumber nvarchar(50),@InvoiceDate datetime ,@InvoiceDate1 datetime, @AccountNumber nvarchar(50),@TradingPartnerID nvarchar(50),@Page nvarchar(50),@rownum int OUTPUT)ASBEGINSELECT * 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 InvoiceHeaderWHERE (@InvoiceNumber = '' OR DocumentNumber = @InvoiceNumber)and(@AccountNumber = '' or AccountNumber = @AccountNumber)and(@TradingPartnerID = '' or TradingPartnerID = @TradingPartnerID)and(@InvoiceDate = '' or DocumentDate >= @InvoiceDateandDocumentDate <=@InvoiceDate1 or @InvoiceDate1 = ''))As qrWHERE rowno BETWEEN ((@Page - 1) * 20 + 1)AND (@Page * 20)END[/code]Thu, 13 Sep 2012 02:38:15 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxHi thanks for the reply. Unfortunately that just returns the row count of the rows that i have asked for i.e always 20 as thats the limit i've set. Any other suggestions:-DThu, 13 Sep 2012 02:22:43 GMTmark.dungey 56406http://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxGive this a try:[code="sql"]Alter proc [dbo].[InvoiceDisplayTest] ( @InvoiceNumber nvarchar(50), @InvoiceDate datetime , @InvoiceDate1 datetime, @AccountNumber nvarchar(50), @TradingPartnerID nvarchar(50), @Page nvarchar(50), @rownum int OUTPUT)ASBEGIN SELECT * FROM (SELECT InvoiceID,DocumentNumber, convert(char(12),DocumentDate,112) AS DocumentDate,AccountNumber,Supplier,TradingPartnerID, ROW_NUMBER()OVER(ORDER BY DocumentNumber ASC)AS rowno 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) SET @rownum = @@rowcount;END[/code]Wed, 12 Sep 2012 09:59:33 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1358078-391-1.aspxHi Below is the code for my stored procedureAlter proc [dbo].[InvoiceDisplayTest] (@InvoiceNumber nvarchar(50),@InvoiceDate datetime ,@InvoiceDate1 datetime, @AccountNumber nvarchar(50),@TradingPartnerID nvarchar(50),@Page nvarchar(50),@rownum int OUTPUT)ASBEGINSELECT * FROM (SELECT InvoiceID,DocumentNumber, convert(char(12),DocumentDate,112) AS DocumentDate,AccountNumber,Supplier,TradingPartnerID, ROW_NUMBER()OVER(ORDER BY DocumentNumber ASC)AS rowno FROM InvoiceHeaderWHERE (@InvoiceNumber = '' OR DocumentNumber = @InvoiceNumber)and(@AccountNumber = '' or AccountNumber = @AccountNumber)and(@TradingPartnerID = '' or TradingPartnerID = @TradingPartnerID)and(@InvoiceDate = '' or DocumentDate >= @InvoiceDateandDocumentDate <=@InvoiceDate1 or @InvoiceDate1 = ''))As qrWHERE rowno BETWEEN ((@Page - 1) * 20 + 1)AND (@Page * 20)ENDMy Question is how do I SET my @rownum OUTPUT parameter equal to the total rows for the inner select statement. As the rows for the whole query only counts the ones being displayed. Cant i just use the alias of my ROW_NUMBER() to get the total rows. Or another way.Looking foward to hearing from youMarkWed, 12 Sep 2012 09:51:55 GMTmark.dungey 56406