August 7, 2015 at 10:00 am
Hi ,
I am having a strange issue not sure if this is MSSQL bug or mine 🙁
I have following simple code in my stored proc. even I have hard coded OFFSET to non zero, but it always return result from starting point 0. End limit "Fetch Next" is working perfect. Only problem is with start.
SELECT
*
FROM #invoices
ORDER BY #invoices.InvoiceDateTime ASC
OFFSET @StartRow ROWS Fetch NEXT @EndRow ROWS ONLY;
August 7, 2015 at 10:19 am
declare @StartRow int = 10, @EndRow int = 10
drop table #Temp;
;WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1, n1 n),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM n2, n2 n)
SELECT IDENTITY(int,1,1) AS ID,
SomeDate,
SomeJunk = CAST(NEWID() AS VARCHAR(36))
INTO #Temp
FROM iTally
CROSS APPLY (SELECT SomeDate = DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))%1440,DATEADD(day,n-10000,GETDATE()))) x
ORDER BY SomeDate;
CREATE UNIQUE CLUSTERED INDEX ucx_SomeDate ON #Temp (SomeDate);
-- Return rows with ID 11 through 20
-- @StartRow and @EndRow are poor names for these variables
-- @Offset and @Batchsize might be better
SELECT *
FROM #Temp
ORDER BY #Temp.SomeDate ASC
OFFSET @StartRow ROWS
Fetch NEXT @EndRow ROWS ONLY;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 7, 2015 at 12:49 pm
Thank you Chris. I agree that your code is working.
My code is working too, but problem is offset always start with 0. everything seems ok. so I asked if I need consider some other factors.
August 7, 2015 at 12:58 pm
How are you setting @StartRow? It looks like it's not being set properly then.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 7, 2015 at 1:07 pm
it is setting correct ,as i have confirmed it via print
August 7, 2015 at 2:08 pm
How about posting the entire SQL script or procedure that you are have difficulty with. From what you have posted we really can't give much of an answer.
August 10, 2015 at 7:00 am
Sure, here is code. I have removed some business rules, as I can't post entire code here.
ALTER procedure [dbo].[spPayment]
@CustomerNo int,
@StartDate datetime,
@EndDate datetime,
@InvoiceNo varchar(20),
@pageSize int =null,
@PageNum int =null,
@SortDirection varchar(4) = 'ASC'
as
begin
--Declare Variable for pagination
declare @Rows int,
@StartRow int,
@EndRow int;
create table #inv(
InvoiceNo varchar(20),
CustomerPo varchar(20),
InvoiceDateTime datetime
);
insert into #inv
select
Col1,col2,col3
from Invoices invc with (NOLOCK)
union
select
Col1,col2,col3
from invoices2 invc2 with (NOLOCK);
-- Setting varibale value to get count of rows
set @rows =@@rowcount; -- Setting total Rows
set @StartRow = (isnull(@PageSize,0)*isnull(@PageNum,1))-isnull(@PageSize,0);
set @EndRow = @StartRow + isnull(@PageSize,@Rows);
select @StartRow as StartRows,@EndRow as EndRows,@Rows/@PageSize as TotalPages; -- this select is showing correct startrow and endRow.
SELECT
*
FROM #inv
ORDER BY #inv.InvoiceDateTime ASC
OFFSET @StartRow ROW Fetch FIRST @EndRow ROWS ONLY; -- this is considering @endRow , but fetching results from Offset 0
End
August 10, 2015 at 7:24 am
Have you checked the data by eye to see if it's actually going to work?
create table #inv(
InvoiceNo varchar(20),
CustomerPo varchar(20),
InvoiceDateTime datetime
);
insert into #inv
select
Col1,col2,col3
from Invoices invc with (NOLOCK)
union
select
Col1,col2,col3
from invoices2 invc2 with (NOLOCK);
--select @StartRow as StartRows,@EndRow as EndRows,@Rows/@PageSize as TotalPages; -- this select is showing correct startrow and endRow.
SELECT TOP 1000 *
FROM #inv
ORDER BY #inv.InvoiceDateTime ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 7:32 am
Yes, I have confirmed. Overall data set size in temp table is ~100k rows.
When I set offset it terminate on EndRows , but always start from 0. e.g. if I set page size 10 and pageNumer 2. It return 20 rows instead 10.If I set EndRow 40, it return 40 records
August 10, 2015 at 7:51 am
thbaig (8/10/2015)
Yes, I have confirmed. Overall data set size in temp table is ~100k rows.When I set offset it terminate on EndRows , but always start from 0. e.g. if I set page size 10 and pageNumer 2. It return 20 rows instead 10.If I set EndRow 40, it return 40 records
I think you misunderstand how the ORDER BY extensions work.
OFFSET @StartRow ROWS means "discard the first @StartRow rows"
Fetch NEXT @EndRow ROWS ONLY means "beginning with the first row (after discarding the first @StartRow rows), return the next @EndRow rows".
That's why I recommended you change the names of those variables. They don't match what they logically represent. @EndRow should be "@Batchsize", as it's the number of rows returned.
@StartRow is the number of rows discarded.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 8:03 am
This might help:
DECLARE @pageSize INT, @PageNum INT
SELECT @pageSize = 60, @PageNum = 1
SELECT * FROM #inv ORDER BY #inv.InvoiceDateTime ASC
OFFSET 0 ROWS FETCH FIRST 60 ROWS ONLY
SELECT @pageSize = 60, @PageNum = 2
SELECT * FROM #inv ORDER BY #inv.InvoiceDateTime ASC
OFFSET 60 ROWS FETCH FIRST 60 ROWS ONLY
SELECT @pageSize = 60, @PageNum = 3
SELECT * FROM #inv ORDER BY #inv.InvoiceDateTime ASC
OFFSET 120 ROWS FETCH FIRST 60 ROWS ONLY
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 8:39 am
Thank you Chris. You are correct and initially I had same understanding, but I de-tracked 🙁
Thanks again
August 10, 2015 at 10:08 am
thbaig (8/10/2015)
Thank you Chris. You are correct and initially I had same understanding, but I de-tracked 🙁Thanks again
No worries, I have days like that too - more as the grey hair overtakes the brown 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 10, 2015 at 12:24 pm
thbaig (8/10/2015)
Yes, I have confirmed. Overall data set size in temp table is ~100k rows.When I set offset it terminate on EndRows , but always start from 0. e.g. if I set page size 10 and pageNumer 2. It return 20 rows instead 10.If I set EndRow 40, it return 40 records
Just an FYI, if you ever fail to supply a value for PageSize to the proc, you will indeed start at 0, as the variable will be set to 0 as a result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply