数据库分页存储过程

树立好如下的存款和储蓄进程,以往要分页,直接调用改存款和储蓄进程就能够了。
注意:数据量大、质量需求高的,请个性化管理。 复制代码 代码如下: ALTELacrosse PROCEDURE
[dbo].[COMMON_PROCEDURE_SelectWithPage] @Sql VARCHAHighlander(5000卡塔尔国,
@CurrentPageNo INT, @PageSize INT, @TotalNum INT OUTPUT AS SET NOCOUNT
ON DECLARE @SqlCmd VARCHA哈弗(5000卡塔尔(قطر‎—————————————— –查询数据 SET @SqlCmd =
‘SELECT * FROM (‘ + @Sql + ‘) A WHERE RowIndex BETWEEN ‘ +
CONVERT(VARCHAR,(@CurrentPageNo-1) * @PageSize + 1) + ‘ AND ‘ +
CONVERT(VARCHAR,@CurrentPageNo * @PageSize卡塔尔(قطر‎ EXEC(@SqlCmdState of Qatar P昂科雷INT
(@SqlCmd卡塔尔 —————————————— –求记录总量 IF
@TotalNum = -1 BEGIN CREATE TABLE #Temp1(num INT) INSERT INTO #Temp1
EXEC(‘SELECT count(*) FROM (‘ + @Sql + ‘) A’) SELECT @TotalNum=(SELECT
* FROM #Temp1) DROP TABLE #Temp1 END
用法很简短,但必须要在传诵的SQL中接受ROW_NUMBER() OVER(…) AS RowIndex
: DECLARE @Sql VARCHAR(5000) DECLARE @CurrentPageNo INT DECLARE
@PageSize INT DECLARE @TotalNum INT SET @CurrentPageNo = 100 SET
@PageSize = 10 SET @TotalNum = -1 SET @Sql = ‘ SELECT *, ROW_NUMBE锐界(State of QatarOVE宝马7系 (O路虎极光DE福特Explorer BY 排序字段卡塔尔 AS RowIndex FROM 表名 A WITH (NOLOCK卡塔尔(قطر‎ ‘ EXEC
[dbo].[COMMON_PROCEDURE_SelectWithPage]
@Sql,@CurrentPageNo,@PageSize,@TotalNum OUTPUT SELECT @TotalNum

CREATE PROCEDURE [dbo].[P_DOCGetTest]–20,2,0

@orderCellName NVARCHAR(50),

@sortOrder NVARCHAR(50),

@pageSize INT,

@pageIndex INT,

@RowCount INT OUTPUT

AS

BEGIN

  DECLARE @SQL NVARCHAR(MAX)

  DECLARE @SQL_COUNT NVARCHAR(MAX)

  DECLARE @BEGIN INT

   DECLARE @END INT

   SET @BEGIN = CAST(((@PageIndex – 1) * @PageSize + 1) AS
VARCHAR(15))

   SET @END = CAST((@PageSize * @PageIndex) AS VARCHAR(15))

  IF(ISNULL(@orderCellName,”)=”)

   BEGIN

     SET @orderCellName=’T.Id’

   END