- 作者:zhaozj
- 发表时间:2020-12-23 10:56
- 来源:未知
CREATE PROC Turnpage @qCols varchar(200), --需要查询的列 @qTables varchar(200), --需要查询的表 和条件 @iKey varchar (20), --标识字段 @oKey varchar(20), --排序字段 @pageSize int, --每页的行数 @pageNumber int --要显示的页码, 从0开始 AS set nocount on BEGIN DECLARE @sqlText AS varchar(1000) DECLARE @sqlTable AS varchar(1000) SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber + 1) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + ' order by '+@oKey + ' desc' SET @sqlText = 'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' + 'FROM (' + @sqlTable + ') AS tableA ' + 'WHERE ' + @iKey + ' NOT IN(SELECT TOP ' + CAST(@pageNumber * @pageSize AS varchar(30)) + ' ' + @iKey + ' FROM (' + @sqlTable + ') AS tableB)' EXEC (@sqlText) --print(@sqltext) END GO