Microsoft SqlServer 2005 通用分页存储过程-优快云博客

本来我是不赞成使用通用存储过程的，主要是因为根据表结构来定制分页查询不用动态的拼SQL，这样才是真正的高效，而且只要写过一个，那么再有新需求的时候，小范围改动几处就ok了。

但总是有人向我讨要或者讨论通用存储过程，没办法，被逼无奈，良心丧与困境。

木有找到T-SQL代码编辑器

-- ============================================= -- Author: 袁立强 -- Create date: 2006-5-11 -- Description: SQL 2005 版本通用分页存储过程 -- For: FirstDM -- BLOG: http://blog.youkuaiyun.com/ylqmf -- ============================================= CREATE PROCEDURE [dbo].[Pagination] @Table nvarchar(4000) = N'select * from sys.objects', @ID nvarchar(200) = N'[object_id]', @PageSize int = 20, @PageIndex int = 1, @RowCount int OUTPUT as if @PageIndex<1 begin set @PageIndex=1; end if @PageSize<1 begin set @PageSize = 20; end declare @begin int declare @end int set @begin=(@PageIndex-1)*@PageSize+1; set @end = @PageIndex*@PageSize; declare @from nvarchar(4000) declare @sql nvarchar(4000) declare @index int select @index = PATINDEX(N'%from%',@table); if @index = 0 begin set @from = N'from '+@Table; set @sql= N'select * from '+@Table; end else begin set @from = SUBSTRING(@table,@index,LEN(@table)-@index+1); set @sql = @Table; end declare @parameters nvarchar(50) set @parameters = '@RowCount int OUTPUT'; declare @SQLString nvarchar(max) select @SQLString =N' declare @Tpage table ( id bigint, inx bigint identity(1,1) PRIMARY KEY ); with cte_page as ( select DISTINCT '+@id+N' as id '+@from+N' ) insert @Tpage select * from cte_page; with cte_temp as ( '+@sql+N' ) select a.* from cte_temp as a inner join @Tpage as b on b.id = a.'+@ID+N' where b.inx between '+CONVERT(nvarchar(100), @begin)+N' and '+CONVERT(nvarchar(100), @end)+N'; with cte_count as ( select top 1 inx from @Tpage order by inx desc ) select @RowCount = inx from cte_count; ' exec sp_executesql @SQLString , @parameters ,@RowCount=@RowCount OUTPUT go