USE [data_smf]
GO
/****** 对象:  StoredProcedure [dbo].[page]    脚本日期: 01/10/2011 15:01:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[page]
@key int=0, --返回总页数或数据
@value nvarchar(50)='-',--用于排序字段
@size int=20, --每页显示数据的条数
@order nvarchar(50)='asc',
@table nvarchar(50),
@column nvarchar(100)
 AS

 SET NOCOUNT ON;
--declare @KEYS int
declare @sqlstr1 nvarchar(2000)
declare @sqlstr2 nvarchar(2000)
declare @sqlstr3 nvarchar(2000)
--set @KEYS=@key
set @sqlstr1='select RowNumber=ceiling(count(id) *1.0/'+cast(@size as nvarchar(20))+')from ' +@table +' '--总页数
set @sqlstr2= 'select '+@column+' from (select row_number() over(order by '+@value+' '+@order+  ' ) as RowNum, *  from  '+@table+' ) t where t.RowNum between 20*('+cast(@key as nvarchar(20))+'-1) and 20*'+cast(@key as nvarchar(20))+'order by '+' '+@value+' '+@order+' '

--id可以被替换

BEGIN
--返回总行数
if(@key=0)
begin
exec(@sqlstr1)
end
--返回调用页的数据
 if(@key>0 and @value<>'-')
begin
  exec(@sqlstr2)
end
--返回总页数，并且返回第一页数据
if(@key=-1)
begin
exec(@sqlstr1)
-- 设置key 值无效
set @key=1
 exec(@sqlstr2)

end
END