存储过程分面

create proc GetItems
(
    @id  int
    ,@cid int
    ,@PageIndex int = 0 
    ,@PageSize int = 20 
    ,@TotalRecords int output
)
as
begin
    declare @ResultTable table
    (
        RowIndex int
        ,[id] int 
        ,[title]        varchar(25)  not null            --标题
        ,[Cutoff]        datetime        not null        --截止时间
        ,[url]            varchar(500)    not null        --连接地址
        ,[imgfile]        varchar(500)    not null        --图片连接
        ,[Oriprice]        numeric(8,2)    not null        --原价
        ,[price]        numeric(8,2)    not null        --当前价格
        ,[cid]            int                not null        --类型
    );
    
    Insert into @ResultTable 
    select row_number() over (order by id desc) as RowIndex, 
        [id]
        ,[title]    
        ,[Cutoff]        
        ,[url]            
        ,[imgfile]        
        ,[Oriprice]        
        ,[price]        
        ,[cid]            
        from [Items]
            where  id=(
                case when @id is not null or @id<=0
                then @id
                else
                    [id]
                    end
            )
            and cid=(
                case when @cid is not null or @cid<=0
                then @cid
                else
                    [cid]
                    end
            )
            
    select  @TotalRecords = count(*) from  @ResultTable; 
    
    select * 
        from   @ResultTable 
        where  RowIndex > (@PageSize * (@PageIndex-1)) 
                and RowIndex <= (@PageSize * (@PageIndex));
end