Increase paging query stored procedures using temporary tables and @ @RowCount in SQL Server 2005

The recent discovery of the performance of common query stored procedures for existing frameworks is slow, and the following code is carefully studied:

Alter PROCEDURE [dbo]. [Areaselect]


@PageSize int=0,


@CurrentPage Int=1,


@Identifier Int=null,


@ParentId Int=null,


@AreaLevel Int=null,


@Children Int=null,


@AreaName nvarchar (=null),


@Path nvarchar (MAX) =null,


@Status Int=null,


@Alt Int=null


as


BEGIN


SET NOCOUNT on;


IF (not @AreaName is NULL) SET @AreaName = '% ' + @AreaName + '% '


IF (not @Path is NULL) SET @Path = '% ' + @Path + '% '


IF (@PageSize >0)


BEGIN


DECLARE @TotalPage int


Select @TotalPage =count (Identifier) from the area Where


(@Identifier is NULL or identifier= @Identifier) and


(@ParentId is NULL or parentid= @ParentId) and


(@AreaLevel is NULL or arealevel= @AreaLevel) and


(@Children is NULL or children= @Children) and


(@AreaName is NULL or areaname like @AreaName) and


(@Path is NULL or Path like @Path) and


(@Status is NULL or status= @Status) and


(@Alt is NULL or alt= @Alt)


IF (@TotalPage% @PageSize =0)


BEGIN


SET @TotalPage = @TotalPage/@PageSize


End


ELSE


BEGIN


SET @TotalPage =round (@TotalPage/@PageSize, 0) +1


End


Select Top (@PageSize) Identifier,parentid,arealevel,children,areaname,path,status,alt, @TotalPage as Totalpage From area Where


Identifier not in (the Select Top (@PageSize * (@CurrentPage-1)) Identifier from area Where


(@Identifier is NULL or identifier= @Identifier) and


(@ParentId is NULL or parentid= @ParentId) and


(@AreaLevel is NULL or arealevel= @AreaLevel) and


(@Children is NULL or children= @Children) and


(@AreaName is NULL or areaname like @AreaName) and


(@Path is NULL or Path like @Path) and


(@Status is NULL or status= @Status) and


(@Alt is NULL or alt= @Alt)


ORDER BY areaname ASC)


and


(@Identifier is NULL or identifier= @Identifier) and


(@ParentId is NULL or parentid= @ParentId) and


(@AreaLevel is NULL or arealevel= @AreaLevel) and


(@Children is NULL or children= @Children) and


(@AreaName is NULL or areaname like @AreaName) and


(@Path is NULL or Path like @Path) and


(@Status is NULL or status= @Status) and


(@Alt is NULL or alt= @Alt)


ORDER BY areaname ASC


End


ELSE


BEGIN


Select Identifier,parentid,arealevel,children,areaname,path,status,alt from area Where


(@Identifier is NULL or identifier= @Identifier) and


(@ParentId is NULL or parentid= @ParentId) and


(@AreaLevel is NULL or arealevel= @AreaLevel) and


(@Children is NULL or children= @Children) and


(@AreaName is NULL or areaname like @AreaName) and


(@Path is NULL or Path like @Path) and


(@Status is NULL or status= @Status) and


(@Alt is NULL or alt= @Alt)


ORDER BY areaname ASC


End


End