September 12, 2012 at 9:51 am
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
September 12, 2012 at 9:59 am
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
September 13, 2012 at 2:22 am
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
September 13, 2012 at 2:38 am
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/61537September 13, 2012 at 2:58 am
Nope thats absolutely fine. I just wanted a way to return the total number of rows to php for pagination. Perfect thanks very much
September 13, 2012 at 3:22 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply