Using ROW_COUNT

  • Hi Below is the code for my stored procedure

    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 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

    My 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 you

    Mark

  • Give this a try:

    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

    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

  • Hi 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:-D

  • 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
  • Nope thats absolutely fine. I just wanted a way to return the total number of rows to php for pagination. Perfect thanks very much

  • You might also want to have a read through this: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply