declare @SQLStr varchar(8000) set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )' exec(@SQLStr)
CREATE procedure Department_pagination @SelectStr nvarchar(1000), @ColumnStr nvarchar (1000), @OrderStr nvarchar (1000), @CurrentPage int, @PageCount int as declare @TimeName nvarchar(25) declare @TableStr nvarchar(1000)select @TimeName = convert(nvarchar(23), getdate(), 121) set @TimeName = REPLACE(@TimeName, '.', '') set @TimeName = REPLACE(@TimeName, ':', '') set @TimeName = REPLACE(@TimeName, '-', '') set @TimeName = REPLACE(@TimeName, ' ', '')select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,' exec(@TableStr+@ColumnStr+')') exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr) exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb') exec('drop table ##Tab' + @TimeName) GO参数1:select语句。2:字段列表。3:排序字段。4:当前页。5每页数目
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)
@SelectStr nvarchar(1000),
@ColumnStr nvarchar (1000),
@OrderStr nvarchar (1000),
@CurrentPage int,
@PageCount int
as
declare @TimeName nvarchar(25)
declare @TableStr nvarchar(1000)select @TimeName = convert(nvarchar(23), getdate(), 121)
set @TimeName = REPLACE(@TimeName, '.', '')
set @TimeName = REPLACE(@TimeName, ':', '')
set @TimeName = REPLACE(@TimeName, '-', '')
set @TimeName = REPLACE(@TimeName, ' ', '')select @TableStr='create table ##Tab' + @TimeName + '(wb int identity,'
exec(@TableStr+@ColumnStr+')')
exec('insert into ##Tab' + @TimeName + ' ' + @SelectStr + ' order by ' + @OrderStr)
exec('select * from ##Tab' + @TimeName + ' where wb between ((' + @CurrentPage + '-1)*' + @PageCount + '+1) and ' + @CurrentPage + '*' + @PageCount + ' order by wb')
exec('drop table ##Tab' + @TimeName)
GO参数1:select语句。2:字段列表。3:排序字段。4:当前页。5每页数目