Using Paging Storage Procedure Create Procedure ListPage (@tblname nvarchar (200), --- To display the table or multiple tables of multiple tables @fldname nvarchar (200) = '*', --- List of fields to display @ @- PageSize int = 10, ---- The number of records displayed per page is @Page int = 1, --- To display the record @PageCount Int = 1 Output, ---- Query Results After the total Number of pages @counts int = 1 Output, ---- Query number @fldsort nvarchar (100) = null, ---- Sort field list or condition @Sort bit = 0, ---- Sort Method, 0 To be ascended, 1 is descended @Strcondition nVarchar (200) = null, ---- Query criteria, no WHERE @ ID nvarchar (50) - the primary key of the primary table) Asset NoCount Ondeclare @sqltmp nvarchar (1000) - --- Store dynamically generated SQL statement declare @strtmp nvarchar (1000) ---- Store Query Statement Declare @strid nvarchar (1000) ---- Store gets the query statement Declare @sqlsort nvarchar (200) ---- Store temporary generation sort condition DECLARE @intcounts int ---- To move record number declare @beginid int ---- Start IDDECLARE @endid Int ---- End Id
-------- First, rank first --------- if @ Sort = 0 - Ascending Begin if Not (@fldsort is null) set @sqlsort = 'Order by' @fldsort else Set @Sqlsort = 'Order By' @id endelse - Descending Begin if not (@fldsort is null) set @sqlsort = 'order by' @fldsort 'desc' else set @sqlsort = 'Order by' @ ID 'DESC' END
-------- Generate Query Statement ----------> This is the statement IF @Strcondition is Null - No Set Display Conditions Begin Set @Sqltmp = @fldName 'From' @tblname set @strtmp = 'select @ counts = count (' @id ') from' @ TBLNAME set @strid = 'from' @tblname endelse begin set @sqltmp = @fldname 'From' @TBLNAME 'Where' @Strcondition set @strtmp = 'select @ counter = count (' @id ') from' @ TBLNAME 'WHERE' @Strcondition set @strid = 'from' @TBLNAME 'Where' @strcondition end ---- acquire the total quantity of the query results ----- EXEC SP_EXECUTESQL @strtmp, n '@ counts int out', @ counter Out
- Total number of page number if @counts <= @PageSize set @PageCount = 1ELSE set @Pagecount = (@counts / @pagesize) 1
- Calculate the number of records to be moved if @Page = 1 set @intcounts = @PagesizeElseelseEvalGin set @intcounts = (@ Page-1) * @Pagesize 1END
----- Number of IDSet @strid = 'select @ beginid =' @ID ' @Strid after obtaining paging
Set @intcounts = @intcounts - @Pagesize 1Set RowCount @intcountsexec sp_executesql @ strid, n '@ beginge Int out', @ Beginge Out
----- Number of IDSET @intcounts = @intcounts @Pages = @intcounts @Pagesize - 1Print @intcountsset rowcount @intcountsexec sp_executesql @ strid, n '@ beginger int out', @ endid out @
------ Restore System Settings ----- Set Rowcount 0Set NoCount Off
------ Back Query Results ----- if @Strcondition Is Null Set @Strtmp = 'SELECT' @Sqltmp 'Where' @id 'Between' Str (@beginid) 'and' STR (@endid) else set @STRTMP = 'SELECT' @Sqltmp 'Where' @id '(Between' Str (@Beginid) 'and' str (@ENDID) ') and' @Strconditionif Not (@sqlsort is null) set @strtmp = @STRTMP @Sqlsortexec sp_executesql @STRTMP
Go
