Home > SQL Server > NULL keys and ROW_NUMBER

NULL keys and ROW_NUMBER

2012/07/11

I wrote a stored procedure for gridview paging in SQL server 2012, based on a view, something as

SELECT [Id], [IdAdvice], [other fields..] FROM ( 
   SELECT [Id],[IdAdvice], [other fields..] , ROW_NUMBER() OVER (ORDER BY Surname) as RowNum FROM viewOperators
) AS Dataset 
WHERE IdAdvice= @IdAdvice
AND RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY Surname

I was surprised because the @pageSize is 10 and in the first page i was seeing 8 records.

Other similar gridviews instead ok.

The problem was that in the table Operators i have 2 special administrative operators (for the MemberShip, done with a custom provider) where the idAdvice is NULL, specifying in the view (viewOperators) a fixed filter

WHERE idAdvice IS NOT NULL 

the paging is correct.

So when creating a SQL query for custom paging beware of NULL keys, if the fields are part of the filter for the query.

Advertisements
Categories: SQL Server
%d bloggers like this: