Thread: Using ROW_COUNT

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    8
    Rep Power
    0

    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 on 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
    Last edited by markyboy17; September 12th, 2012 at 11:45 AM. Reason: Couldn't see the code

IMN logo majestic logo threadwatch logo seochat tools logo