sql分页小程序

alter proc PR_Base_ShowPage
@PageSize int,
@Page    int ,
@TableName varchar(30),
@OrderColumn varchar(20)

/*
功能：分页取表的数据
参数：
    @PageSize   页大小
    @Page       第几页，从1开始
    @TableName  表名
    @OrderColumn 排序列名
*/

as
begin

declare @strsql varchar(2000)
declare @Count varchar(10)
declare @pre varchar(10)
set @pre=cast((@PageSize*(@Page-1)) as varchar)
set @Count=cast((@PageSize*@Page) as varchar)
set @strsql='select b.* from (select top '+@pre+' * from '+@TableName+' order by '+@OrderColumn+') a
right join (select top '+@Count+' * from '+@TableName+' order by '+@OrderColumn+') b
on a.'+@OrderColumn+'=b.'+@OrderColumn+' where a.'+@OrderColumn+' is null'
if @Page=1
begin
set @strsql=' select top '+cast(@PageSize as varchar)+' * from '+@TableName+' order by '+@OrderColumn
end

exec(@strsql)

end

/*
select b.* from (select top 3000 * from tbBaseCombination order by cnicombinationID) a
right join (select top 4000 * from tbBaseCombination order by cnicombinationID) b
on a.cniCombinationID=b.cniCombinationID where a.cniCombinationid is null
*/