自已写的一个分页的存储过程_自行设计实现一个分页的存储过程。-优快云博客

本文介绍了一个SQL Server中用于实现分页查询的存储过程，该过程通过输入表名、主键名、列名列表、页大小、页号及筛选条件等参数，动态生成SQL语句来获取指定页面的数据，并返回总记录数。

/****** 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)