Sql Server Paging Store Procedure

In asp.net default paging option available in GridView ,Listview ,Fromview. These controls do manage paging automatically without writing so much code but provide poor performance. This default paging is handled in state management.  This approach is suitable where data is less. If you have millions record in your table you need to database server side paging otherwise your application suffering performance issue.

So let’s create the table with sample data:

CREATE TABLE [dbo].[Product] (

    [Id]          INT            IDENTITY (1, 1) NOT NULL,

    [Name]        NVARCHAR (256) NULL,

    [CategoryId]  INT            NULL,

    [Description] NVARCHAR (512) NULL,

    [IsActive]    BIT            NULL,

    [Price]       DECIMAL (18)   NOT NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [FK_Product_Category] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[Category] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE

);

//Insert Data

SET IDENTITY_INSERT [dbo].[Product] ON

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (1, N'Htc on', 1, NULL, 1, CAST(34 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (2, N'Samsung  galaxy', 1, N' Thids jsdfhns kndfksd', 1, CAST(45 ASDecimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (3, N'Lumia 1320', 1, N'test test', 1, CAST(45 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (4, N'Ramond', 2, N' theisbdf ', 1, CAST(45 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (5, N'Peter England trouser', 2, N'sdfj', 1, CAST(4 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (6, N'Niva', 3, N'dhfdh', 1, CAST(34 AS Decimal(18, 0)))

INSERT INTO [dbo].[Product] ([Id], [Name], [CategoryId], [Description], [IsActive],[Price]) VALUES (7, N'Ponds', 3, NULL, 1, CAST(45 AS Decimal(18, 0)))

SET IDENTITY_INSERT [dbo].[Product] OFF

create PROCEDURE [dbo].[Paging]

       @Page int = 0,

       @PageSize int,

       @TotalCount int,

       @SortByCloumnName varchar(128),

       @SortByDirection  varchar(4)

AS

     declare @StartRow int , @EndRow int

       set @StartRow = (@page-1)*@PageSize +1;

       set @EndRow   = @StartRow * @PageSize ;

       select @TotalCount = count(*) from Product;

       ;with cte as

       (

          select top(@TotalCount) ROW_NUMBER() over(order by id ) rownumber,*  from  Product   order by 

          case when @SortByCloumnName='Name' and  LOWER( @SortByDirection)= 'asc'  thenName   end asc,

           case when @SortByCloumnName='Name' and LOWER(@SortByDirection) = 'desc' then Name   end Desc

         

        )

         select * from cte  where  rownumber between @StartRow and @EndRow

RETURN 0

Testing store procedure in management studio

EXEC Paging 1, 7,NULL,'Name','Asc'