-- =============================================
--http://www.flyso.cn
-- =============================================
Create PROCEDURE [dbo].[flyso_pagination3]
@Select NVARCHAR(500),   -- 要查询的列名，用逗号隔开（Select后面From前面的内容）
@From NVARCHAR(200),   -- From后的内容
@Where NVARCHAR(500) = NULL, -- Where后的内容
@OrderBy NVARCHAR(100) = NULL, -- 排序字段
@Key NVARCHAR(50),    -- 分页主键
@Page INT,      -- 当前页 ***计数从1开始***
@PageSize INT,     -- 每页大小
@TotalCount INT OUTPUT,   -- 总记录数
@Result TINYINT OUTPUT   -- 执行是否成功
AS
BEGIN
SET NOCOUNT ON;

Declare @Sql nVarchar(1000), @Sql2 NVARCHAR(500)

--Alter By Tracy.Chuang 2006-08-21更改分页算法，采用比较最大值的方法
Set @Sql=
'Select Top '+Cast(@PageSize As
nVarchar(10))+' '+@Select+  ' From '+@From+  ' Where '+Case
IsNull(@Where,'') When '' Then '' Else @Where+' And ' End+
@Key+' >( Select ISNULL(MAX('+@Key+'), 0) AS MaxID
    From (Select Top '+Cast(@PageSize*(@Page-1) As Varchar(10))+'
'+@Key+
       ' From '+@From+
       Case IsNull(@Where,'') When '' Then '' Else ' Where '+@Where End+
       ' order By '+@Key+') As T)'+
' order By '+@Key+Case IsNull(@OrderBy,'') When '' Then '' Else
','+@OrderBy End

Exec(@Sql)

Set @Sql2='Select 1 As Count From '+@From+Case IsNull(@Where,'') When
  '' Then '' Else ' Where '+@Where End
Exec(@Sql2)
SET @TotalCount = @@ROWCOUNT

SET @Result = 0
END