通用sql分页脚本

来源:互联网 发布:陈华亭去世原因知乎 编辑:程序博客网 时间:2024/03/29 10:09

 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROC [dbo].[P_viewPage_A]
@TableName VARCHAR(200),     --表名
@RecorderCount INT,          --记录总数 0:会返回总记录
@PageSize INT,               --每页输出的记录数
@PageIndex INT,              --当前页数
@TotalCount INT OUTPUT,      --记返回总记录
@TotalPageCount INT OUTPUT   --返回总页数
AS
SET NOCOUNT ON
 IF ISNULL(@TotalCount,'') = ''
  BEGIN
   SET @TotalCount = 0
  END
 IF ISNULL(@TableName,'') = '' OR @RecorderCount  < 0 OR @PageSize < 0 OR @PageIndex < 0
  BEGIN
   PRINT('ERR_00')
   RETURN
  END

DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)

SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName
 IF @RecorderCount  = 0
  BEGIN
   EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
   @TotalCount OUTPUT,@TotalPageCount OUTPUT
  END
 ELSE
  BEGIN
   SELECT @TotalCount = @RecorderCount
  END
 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
  BEGIN
   SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)
  END
 IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
  BEGIN
   IF @PageIndex = 1 --返回第一页数据
    BEGIN
     SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' * FROM '
     + @TableName
    END
   IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据
    BEGIN
     SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ('
     + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
     + ' * FROM '
     + @TableName + ' ) AS TMP ORDER BY FCANDIDATEID DESC'
    END
  END
EXEC(@Sql)

DECLARE @TotalCount INT  
DECLARE @TotalPageCount INT   
EXEC [P_viewPage_A] 'TCandidate',10,5,5,@TotalCount OUTPUT,@TotalPageCount OUTPUT

原创粉丝点击