SQL Server 分页存储过程

USE [ReportServerTempDB]
GO
CREATE PROCEDURE [dbo].[SeachTablePage]
(
@TableName VARCHAR(200),-- 表名
@Fileds VARCHAR(500),-- 查询的字段
@OrderFiled VARCHAR(100),-- 排序字段
@IsDesc BIT ,-- 是否降序排序
@WhereString VARCHAR(2000), -- 查询字段
@PageIndex INT ,-- 当前页数
@PageSize INT ,-- 每页条数
@TotalRecord INT OUTPUT-- 返回总条数
)
AS
BEGIN
DECLARE @OrderString VARCHAR(500)

IF(@PageIndex IS NULL OR @PageIndex <= 0)
BEGIN
SET @PageIndex = 1
END

IF(@PageSize IS NULL OR @PageSize <= 0)
BEGIN
SET @PageSize = 10
END

DECLARE @StartRowID INT
DECLARE @EndRowID INT
SET @StartRowID = (@PageIndex - 1) * @PageSize + 1
SET @EndRowID = @PageIndex * @PageSize

IF (@WhereString is null OR @WhereString = '')
BEGIN
 SET @WhereString = '1 = 1'
END

IF (@OrderFiled IS NULL OR @OrderFiled = '')
BEGIN
SET @OrderFiled = 'CreateDate'
END

IF (@IsDesc IS NULL OR @IsDesc = 1)
BEGIN
SET @OrderString = @OrderFiled + ' DESC'
END
ELSE
BEGIN
SET @OrderString = @OrderFiled + ' ASC'
END

DECLARE @TotalSQL NVARCHAR(2000)
SET @TotalSQL = 'SELECT @Total = COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereString + ''
EXEC sp_executesql @TotalSQL , N'@Total BIGINT OUT' , @TotalRecord OUTPUT -- 返回总记录数

DECLARE @SelectSQL NVARCHAR(3000)
IF(@TotalRecord <= @PageSize AND @PageIndex = 1)
BEGIN
SET @SelectSQL = 'SELECT ' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereString + ' ORDER BY ' + @OrderString
END
ELSE
BEGIN
SET @SelectSQL = 'SELECT row_number() OVER (ORDER BY ' + @OrderString + ') AS RowId,' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereString
SET @SelectSQL = 'SELECT * FROM (' + @SelectSQL + ') AS tab WHERE RowId BETWEEN ' + ltrim(STR(@StartRowID)) + ' AND ' + ltrim(STR(@EndRowID)) + ''
END
print @SelectSQL
EXEC (@SelectSQL)
END