@manhnguyenv

Welcome to my blog!

SQL Paging

July 24, 2017 14:10

CREATE TABLE [dbo].[Customers](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Name] [NVARCHAR](100) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]

GO


INSERT INTO dbo.Customers ( Name )
SELECT 'Customer 1' UNION ALL
SELECT 'Customer 2' UNION ALL
SELECT 'Customer 3' UNION ALL
SELECT 'Customer 4' UNION ALL
SELECT 'Customer 5' UNION ALL
SELECT 'Customer 6' UNION ALL
SELECT 'Customer 7' UNION ALL
SELECT 'Customer 8' UNION ALL
SELECT 'Customer 9' UNION ALL
SELECT 'Customer 10' UNION ALL
SELECT 'Customer 11' UNION ALL
SELECT 'Customer 12' UNION ALL
SELECT 'Customer 13' UNION ALL
SELECT 'Customer 14' UNION ALL
SELECT 'Customer 15' UNION ALL
SELECT 'Customer 16' UNION ALL
SELECT 'Customer 17' UNION ALL
SELECT 'Customer 18' UNION ALL
SELECT 'Customer 19' UNION ALL
SELECT 'Customer 20' UNION ALL
SELECT 'Customer 11'

GO

 

CREATE PROCEDURE [dbo].[GetCustomers]
(
@PageIndex INT, -- Start from 1 not zero
@PageSize INT -- Start from 1 not zero
)
AS
BEGIN

--DECLARE @PageIndex INT -- Start from 1 not zero
--DECLARE @PageSize INT -- Start from 1 not zero
--SET @PageIndex = 1
--SET @PageSize = 1

SELECT *
FROM IECustomers
ORDER BY Id
OFFSET @PageSize * ( @PageIndex - 1 ) ROWS FETCH NEXT @PageSize
ROWS ONLY;

SELECT COUNT(*) AS totalCount
FROM IECustomers;

END;
GO

Categories

Recent posts