CREATE PROCEDURE [dbo].[getPage] @pageSize int, --页尺寸 @currentPage int = 1, --当前页 @fields varchar(2000) = '*', --返回的字段 @tablename varchar(200), --表名 @orderString varchar(1000), --排序字符串 @whereString varchar(1000) --条件字符串ASBEGIN DECLARE @sql varchar(2000) DECLARE @strOrder varchar(2000) DECLARE @strWhere varchar(2000)
set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','') if @strOrder != '' set @strOrder = ' order by ' + @strOrder else set @strOrder = ' order by ID DESC'
set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','') if @strWhere != '' set @strWhere = ' where ' + @strWhere
if @pageSize = 0 set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder else if @currentPage = 1 set @sql = 'select top(' + Str(@pageSize) + ') ' + @fields + ' from ' + @tablename + @strWhere + @strOrder else begin set @sql = 'select top(' + Str(@pageSize) + ') * from (select top(' + Str(@pageSize * @currentPage) + ') ' + @fields + ', ROW_NUMBER() OVER (' set @sql = @sql + @strOrder set @sql = @sql + ') As RowNumber From ' + @tablename set @sql = @sql + @strWhere set @sql = @sql + ') as t where t.RowNumber > ' + Str(@pageSize * (@currentPage - 1)) set @sql = @sql + @strOrder end
exec(@sql)END
