CREATE PROCEDURE [dbo].[page_turning] @Columns varchar(200), --需要查询的列 @TableValue varchar(200), --需要查询的表和条件 @OrderKey varchar(20), --排序字段 @IdentKey varchar(20)='ID', --唯一字段 @PageSize int, --每页的行数 @PageNumber int, --要显示的页码,从 1 开始 @Order varchar(4)='DESC', --排序方式 ASC/DESC @Result int output --返回符合条件的总记录数 AS SET NOCOUNT ON BEGIN DECLARE @MainSql AS varchar(1000) DECLARE @VirtualTable AS varchar(1000) DECLARE @Countable AS nvarchar(1000) DECLARE @Alls AS int SET @Countable = 'SELECT @ALL = COUNT(' + @IdentKey + ')' + ' FROM ' + @TableValue EXEC sp_executesql @Countable,N'@All int output',@Alls output SET @Result=@Alls SET @VirtualTable = 'SELECT TOP ' + CAST(@PageNumber * @PageSize AS varchar(20)) + ' ' + @Columns + ' FROM ' + @TableValue + ' ORDER BY ' + @OrderKey + ' ' + @Order SET @MainSql = 'SELECT TOP ' + CAST(@PageSize AS varchar(20)) + ' * FROM (' + @VirtualTable + ') AS TableA ' + ' WHERE ' + @IdentKey + ' NOT IN (SELECT TOP ' + CAST((@PageNumber-1) * @PageSize AS varchar(20)) + ' ' + @IdentKey + ' FROM (' + @VirtualTable + ') AS TableB)' EXEC(@MainSql) END GO
sql2000 翻页存储过程
最新推荐文章于 2016-11-28 13:54:00 发布