/****** Object: StoredProcedure [dbo].[proc_SplitPage] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[proc_SplitPage] @tblName varchar(255), -- 表名 @strFields varchar(255), -- 显示字段名 @strOrder varchar(255), -- 排序字段名 @strOrderType varchar(50), -- 设置排序类型, asc || desc @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(100) -- 临时变量 declare @strOrderTemp varchar(400) -- 排序类型 if @strOrderType = 'desc' begin set @strTmp = '<(select min' set @strOrderTemp = ' order by [' + @strOrder +'] desc' end else begin set @strTmp = '>(select max' set @strOrderTemp = ' order by [' + @strOrder +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @strOrder + '] from [' + @tblName + ']' + @strOrderTemp + ') as tblTmp)' + @strOrderTemp if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @strOrder + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrderTemp + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderTemp if @PageIndex = 1 begin set @strTmp = '' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' + @tblName + ']' + @strTmp + ' ' + @strOrderTemp end exec (@strSQL) GO