前两天去面试，让写一分页存储过程，嘿，当时竟然忘了怎么写……

回来后写一个记之

use newssystem
go
create procedure pro_news(
	@pageIndex int,
	@pageSize int
)
as 
	declare @startRow int,@endRow int
	set @startRow=(@pageIndex-1)*@pageSize+1
	set @endRow=@startRow+@pageSize-1
	select * from (
		select *,ROW_NUMBER() over (order by id asc)
		as number from news
	) t
	where t.number between @startRow and @endRow;
go

exec pro_news 2,4

下面给一个更好的分页存储过程

/*  
  
    @CurrentPage为显示那一页,  
    @PageSize为每一页显示几行,  
    @Field_info为要显示的字段可以为*,  
    @Table_info为要查询的表或视图,  
    @Field_id 主键或唯一字段,  
    @Field_Order 排序字段,  
    @otherwhere为条件，不带“WHERE”,  
    @RecordCount为总行数, OUTPUT
    @PageCount为总页数, OUTPUT
    @SQLSTR 若发生错误可通过此参数输出SQL语句
  
*/  
CREATE PROCEDURE [dbo].[xhcSp_RecordPager]  
    @CurrentPage int =1,
    @PageSize int = 10,  
    @Field_Info varchar(500),     
    @Table_info varchar(100),  
    @Field_id varchar(20),   
    @Field_Order varchar(100),   
    @otherwhere varchar(8000),   
    @RecordCount int output,   
    @PageCount int output,
    @SQLSTR varchar(8000) output  
AS  
   
begin  
    DECLARE @MinPage int, @MaxPage int 
    declare @sql varchar(8000)   
    declare @sqlt nvarchar(4000)   
   
    set @sqlt = 'SELECT @RecordCount = COUNT(' + @Field_id + ') FROM ' + @Table_Info
    IF @otherwhere != ''
            set @sqlt = @sqlt +' where '+@otherwhere   
   
    exec sp_executesql @sqlt,N'@RecordCount int output',@RecordCount output       
   
    --如何将exec执行结果放入变量中,如果是字符串就要用N,N后面的变量一定要和@sqlt里面的变量同名   
   
    IF @PageSize <= 0   
        begin  
            set @PageSize = 10   
        end  
   
--    else if @PageSize > @RecordCount   
--        begin   
--            set @pageSize = @RecordCount   
--        end   
   
    set @pagecount = @RecordCount / @PageSize   
   
    if ((@recordcount % @pagesize) != 0)                    --如果除不净则加一页   
        begin  
            set @PageCount = @RecordCount / @PageSize   
            set @PageCount = @pagecount + 1   
        end  
    else 
        begin  
            set @pagecount = @recordcount /@PageSize   
        end  
   
    IF @CurrentPage <= 0   
        begin  
            set @CurrentPage = 1   
        end  
   
    else if @CurrentPage > @pagecount   
        begin         
            set @currentpage = @pagecount       --如果输入页数大于总页数则符最后一页   
        end  
   
    SET @MinPage = (@CurrentPage - 1) * @PageSize + 1   
   
    SET @MaxPage = @MinPage + @PageSize - 1   
   
        
   
    BEGIN  
   
        if @Field_Info like '' 
            set @field_Info = '*' 
   
        IF @otherwhere like '' 
            set @sql = 'SELECT top '+str(@PageSize)+' * from
                             (SELECT ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber 
                                from ' + @Table_info + ' 
                             ) as TMP_TABLE where (rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ') '
        ELSE 
           set @sql = 'SELECT top '+str(@PageSize)+' * from 
                            (SELECT ' +  @Field_Info + ' , row_number() over(order by ' + @Field_Order + ') as rownumber 
                                from ' + @Table_info + ' where 1=1 and '+ @otherwhere +'
                            ) as TMP_TABLE where (rownumber between ' + convert(varchar(10),@minpage) + ' and ' + convert(varchar(10),@maxpage) + ') and ' + @otherwhere   
   
        EXEC(@sql)   
        SET @SQLSTR = @sql
 
    END  
   
end

SQL语句调用

USE [DataBaseName]--数据库名
GO

DECLARE	@return_value int,
		@RecordCount int,
		@PageCount int,
		@SQLSTR varchar(8000)

EXEC	@return_value = [dbo].[xhcSp_RecordPager]--存储过程名
		@CurrentPage = 1,
		@PageSize = 10,
		@Field_Info = N'*',
		@Table_info = N'dbo.TableName',--表名
		@Field_id = N'title',
		@Field_Order = N'title',
		@otherwhere = N'1=1',
		@RecordCount = @RecordCount OUTPUT,
		@PageCount = @PageCount OUTPUT,
		@SQLSTR = @SQLSTR OUTPUT

SELECT	@RecordCount as N'@RecordCount',
		@PageCount as N'@PageCount',
		@SQLSTR as N'@SQLSTR'

SELECT	'Return Value' = @return_value

GO