sql2008分页（常用两种方法）

-------------------------------------------------------------------------------------第一种-----------------------------------------------------------------------------

CREATE PROC [dbo].[sp_get_usergroup_by_parentid_page]

@parentId int,
@pageSize int, --每页显示的记录数量
@page int output, --页码数
@totalCount int output, --返回总记录数
@totalPage int output, --返回总的页数
@sort varchar(200) --排序(不带ORDER)
AS
BEGIN

declare @condition varchar(200)
set @condition = ' user_group_status = 1 and user_group_parent_id = '+CAST(@parentId as varchar(200))+' '

SET NOCOUNT ON;

EXEC sp_pager
@page = @page OUTPUT,
@pageSize = @pageSize,
@tableName = 't_user_group',
@columns = N'user_group_id, user_group_name, user_group_sequence, user_group_parent_id,user_group_is_leaf,user_group_status,user_group_created_date',
@condition = @condition,
@sort = @sort,
@totalCount = @totalCount OUTPUT,
@totalPage = @totalPage OUTPUT

END

-------------------------------------------------------------------------------------create sp_pager------------------------------------------------------------------

CREATE Procedure [dbo].[sp_pager]
@page int output, --页码数
@pageSize int, --每页显示的记录数量
@tableName varchar(200), --数据表的名称
@columns varchar(MAX), --要读取的列
@condition varchar(MAX)=NULL, --读取条件(不带WHERE)
@sort varchar(200), --排序(不带ORDER)
@totalCount int output, --返回总记录数
@totalPage int output --返回总的页数
AS
BEGIN
if '' in (@tableName,@columns,@sort)
begin
return
end

set @condition=case when @condition is not null and @condition<>'' then ' where '+@condition else '' end

-- 计算总帖数
declare @sql nvarchar(max)
set @sql ='select @total=count(*) from '+@tablename+@condition
exec sp_executesql @sql,N'@total int out',@totalCount output

-- 计算总页数
set @totalPage=ceiling(1.*@totalCount/@pageSize)

-- 处理@Page参数
set @page=case when @page>@totalPage then @totalPage when @page<1 then 1 else @page end

set @sql='with _x as(select top ('+cast(@page*@pageSize as varchar(20))+') row_number() over(order by '
+@sort+') row_num,'+@columns+' from '+@tableName+@condition+' order by '+@sort+') select '+@columns+' from _x where row_num>'+cast((@page-1)*@pageSize as varchar(10))

print @sql
exec(@sql)
END

-------------------------------------------------------------------------------------第二种-----------------------------------------------------------------------------

CREATE procedure [dbo].[sp_get_configitemid_by_Page]
@configitemid int,
@pageSize int, --每页显示的记录数量
@page int output, --页码数
@totalCount int output, --返回总记录数
@totalPage int output, --返回总的页数
@condition nvarchar(200)=NULL --读取条件(不带WHERE)

as
BEGIN

DECLARE @config_item_id INT
SET @config_item_id = @configitemid

select @totalCount = COUNT(*) FROM
(
SELECT tuir.[user_id]
FROM t_role_config trc
JOIN t_user_in_role tuir ON trc.role_id = tuir.role_id
WHERE trc.config_item_id = @config_item_id
UNION
SELECT tuc.[user_id]
FROM t_user_config tuc WHERE tuc.config_item_id = @config_item_id
) t

create table #viewconfig_get(user_id int ,user_name nvarchar(50),user_comment nvarchar(50),user_created_date datetime,pwd_lastmodify_date smalldatetime)

;WITH _t AS (
SELECT tuir.[user_id],tu.user_name,tu.user_comment,tu.user_created_date,tu.pwd_lastmodify_date
FROM t_role_config trc
JOIN t_user_in_role tuir ON trc.role_id = tuir.role_id join t_user tu on tu.user_id = tuir.user_id
WHERE trc.config_item_id = @config_item_id
UNION
SELECT tuc.[user_id],tu.user_name,tu.user_comment,tu.user_created_date,tu.pwd_lastmodify_date
FROM t_user_config tuc join t_user tu on tu.user_id = tuc.user_id
WHERE tuc.config_item_id = @config_item_id
)
insert #viewconfig_get
SELECT [user_id],user_name,user_comment,user_created_date,pwd_lastmodify_date
--_SELECT tu.user_id,tu.user_name,tu.user_comment,tu.user_created_date,tu.pwd_lastmodify_date
FROM _t
--FROM t_user tu JOIN _t ON tu.[user_id] = _t.user_id

-------------------------------------------------------------------------------------------------

-- 计算总帖数
declare @sql nvarchar(max)
set @sql ='select @total=count(*) from #viewconfig_get'+@condition
exec sp_executesql @sql,N'@total int out',@totalCount output
declare @columns varchar(300),@sort varchar(100)
set @columns = 'user_id,user_name,user_comment,user_created_date,pwd_lastmodify_date'
set @sort = ' user_id asc'

-- 计算总页数
set @totalPage=ceiling(1.*@totalCount/@pageSize)
-- 处理@Page参数
set @page=case when @page>@totalPage then @totalPage when @page<1 then 1 else @page end
set @sql='with _t as(select top ('+cast(@page*@pageSize as varchar(20))+') row_number() over(order by '
+@sort+') row_num,'+@columns+' from #viewconfig_get'+@condition+' order by '+@sort+') select '+@columns+' from _t where row_num>'+cast((@page-1)*@pageSize as varchar(10))

exec(@sql)
END