自已写的一个分页的存储过程

/****** Object:  StoredProcedure [hyc].[TakePage]    Script Date: 03/31/2011 10:58:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [hyc].[TakePage]
 -- Add the parameters for the stored procedure here
 @TableName varchar(500),
 @PriKeyName varchar(200),
 @ColNameList varchar(200)=' * ',
 @PageSize int=10,
 @PageNum int=1,
 @WhereSentence nvarchar(600)=null,
 @TotalRecord int out
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
   
    declare @Sql nvarchar(500)
    if @wheresentence is null
    begin
  set @sql='select @TotalRecord=count(*)'+' from '+@tablename
    end
    else
    begin
  set @sql='select @TotalRecord=count(*)'+' from '+@Tablename +' where '+@Wheresentence
    end

    --返回查询的记录总数，并赋予@TotalRecord
    exec sp_executesql @sql,N'@TotalRecord int out',@TotalRecord out
   

    print @sql
    set @sql='select top '+convert(varchar(20),@PageSize)+' '+@ColNameList+ ' from ('+
  'select row_number() over(order by '+@PriKeyName+') as RowNumber,* from '+@TableName
 if @WhereSentence is null
 begin
  set @sql=@sql+')  a where RowNumber>'+convert(varchar(50),@Pagesize*(@PageNum-1))
 end
 else
 begin
  set @sql=@sql+' where '+@Wheresentence+') a where RowNumber>'+convert(varchar(50),@Pagesize*(@PageNum-1))
 end
 print @sql
 exec(@sql)

  

END

GO