Tuesday, 13 September 2016

Sql paging

DECLARE @PageNumber nvarchar(10) = '5'
DECLARE @PageSize nvarchar(50)  = '10'

IF(ISNULL(@PageNumber,'') = '')
      BEGIN
            SET @PageNumber = '1'
      END

IF(ISNULL(@PageSize,'') = '')
      BEGIN
            SET @PageSize = '10'
      END

DECLARE @query nvarchar(max) = 'SELECT * FROM Vendors'

EXEC ('SELECT * FROM (SELECT * , ROW_NUMBER() OVER(Order By VendorId) AS RowNum         
,COUNT(*) OVER (PARTITION BY NULL) AS PageCount        
FROM ( '+@query + '  )T) T1          
WHERE ROWNUM BETWEEN (' + @PageNumber +'*'+ @PageSize + '+ 1 -' + @PageSize +') AND ('+@PageSize +'*'+ @PageNumber+')' )

1 comment: