分页存储过程

USE [cys]/*使用的数据库*/
GO
/****** 对象:  StoredProcedure [dbo].[CysTablePager_sp]    脚本日期: 07/07/2010 00:42:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        peter-pan
-- Create date: 2009-9-3
-- Description:    分页 在sql2005下适用
-- Alter:        dzd
-- Alter Date:    2010-7-6
-- =============================================
CREATE PROCEDURE [dbo].[CysTablePager_sp]

/*创建的存储过程名称*/
-- Add the parameters for the stored procedure here
---下面是传进来的分页参数-----

@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@tbname nvarchar(50),--表名
@TotalCount int  output--总记录数
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @StrGetFields varchar(1000); -- 需要返回的列
DECLARE @strSql nvarchar(4000); --主语句
DECLARE @FieldOrder varchar(255); -- 排序的字段名
DECLARE @where nvarchar(2000); --where条件
DECLARE @strTmp varchar(110); -- 临时变量
DECLARE @strOrder varchar(400); -- 排序类型
select @StrGetFields=isnull(@StrGetFields+',','')+name from syscolumns where id=object_id(@tbname) and colid between 1 and 8 --获取数据表里的前八列

--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @TotalCount is null
BEGIN
IF(@where != ' where 1=1')
SET @strSql=N'SELECT @TotalCount=COUNT(*)'
+N' FROM '+@tbname
ELSE
SET @strSql=N'SELECT @TotalCount=COUNT(*)'
+N' FROM '+@tbname
EXEC sp_executesql @strSql,N'@TotalCount int OUTPUT',@TotalCount output
END
--------------------------------------------------------------------------排序规则
SET @strOrder = ' ORDER BY id asc'
----------------------------------------------------------------------------第一页
IF @PageIndex < 1
BEGIN
SET @PageIndex = 1
END
IF @PageIndex = 1
BEGIN
SET @strSql = 'SELECT TOP '+ str(@PageSize)+' ' +@StrGetFields + ' FROM ' + @tbname + @strOrder
END
----------------------------------------------------------------------------其他页
ELSE
BEGIN
DECLARE @STARTID nvarchar(50)
DECLARE @ENDID nvarchar(50)
SET @STARTID = convert(nvarchar(50),(@PageIndex - 1) * @PageSize + 1)
SET @ENDID = convert(nvarchar(50),@PageIndex * @PageSize)
SET @strSql =  'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrder + ') AS RowNumber,'+ @StrGetFields + ' FROM '+ @tbname +')
SELECT * FROM MYTABLE WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID

--上面的@strSql先创建了一个临时表MYTABLE,这个表包含了@StrGetFields所标识的列,以及RowNumber个数据.最后一个select表示选择上面的结果的从@STARTID 到@ENDID 之间的数据..
END
PRINT(@strSql)
EXEC(@strSql)
END