Modified a paging stored procedure. (* ^__ ^ *) Xi ......

/* Released Date: 2007-5-30ProcName: UP_GetRecordByPage */-- check UP_GetRecordByPageif exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [UP_GetRecordByPage] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1) drop procedure [dbo]. [UP_GetRecordByPage] GOcreate proc [dbo]. [UP_GetRecordByPage] @ tblName varchar (255), -- table name @ fldName varchar (255), -- Sort field (Multi-field supported, we recommend that you create an index), such as id asc, addtime desc @ PageSize int = 10, -- page size @ PageIndex int = 1, -- page number -- @ IsReCount bit = 0, -- total number of records returned, if the value is not 0, @ TotalPages int output, -- output parameter, total number of returned pages -- @ OrderType bit = 0, -- set the sorting type, if the value is not 0, the query condition is in descending order @ strWhere varchar (1000) = ''(Note: Do not add where) ASBEGIN declare @ strSql nvarchar (4000) declare @ rowsCount int if Ltrim (Rtrim (@ strWhere )) = ''set @ strWhere = '1 = 1' -- obtain RowsCount SET @ strSql = 'select @ TotalRecords = COUNT (*) from '+ @ tblName + 'where' + @ strWhere execute SP_executesql @ strSql, n' @ TotalRecords int output', @ rowsCount output select @ TotalPages = CEILING (@ rowsCount + 0.0) /@ PageSize) if @ PageIndex @ TotalPages SET @ PageIndex = @ TotalPages SET @ strSql = 'select * from (select top (@ PageIndex * @ PageSize) *, ROW_NUMBER () OVER (order by '+ @ fldName +') as rowNumber from '+ @ tblName + 'where' + @ strWhere +') t where t. rowNumber> = (@ PageIndex-1) * @ PageSize + 1) '-- print @ strSql exec sp_executesql @ strSql, n' @ PageIndex int, @ PageSize int', @ PageIndex, @ PageSize END

@ FldName: the sorting field is not the selectList field [*]. The input format is id asc, addtime desc.

Remove the OrderType and IsReCount parameters. OrderType is embodied in @ fldName, and IsReCount has little significance in the original stored procedure. Added an output parameter @ TotalPages.

StrWhere does not need to be added with where