sql.server.asp.net.page.rar_asp.net 控件_page

CREATE PROCEDURE GetRecordFromPage @tblName varchar(255), -- 表名 @RetColumns varchar(1000) = '*', -- 需要返回的列，默认为全部 @Orderfld varchar(255), -- 排序字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType varchar(50) = 'asc', -- 设置排序类型, 非 asc 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(1000) -- 主语句 declare @strTmp varchar(300) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @IsCount != 0 --执行总数统计 begin if @strWhere != '' set @strSQL = "select count(*) as Total from [" + @tblName + "] where " + @strWhere else set @strSQL = "select count(*) as Total from [" + @tblName + "]" end else --执行查询操作 begin if @OrderType != 'asc' begin set @strTmp = "<(select min" set @strOrder = " order by [" + @Orderfld +"] desc" end else begin set @strTmp = ">(select max" set @strOrder = " order by [" + @Orderfld +"] asc" end set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from [" + @tblName + "] where [" + @Orderfld + "]" + @strTmp + "([" + @Orderfld + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @Orderfld + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)" + @strOrder if @strWhere != '' set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from [" + @tblName + "] where [" + @Orderfld + "]" + @strTmp + "([" + @Orderfld + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @Orderfld + "] from [" + @tblName + "] where (" + @strWhere + ") " + @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder if @PageIndex = 1 begin set @strTmp = "" if @strWhere != '' set @strTmp = " where (" + @strWhere + ")" set @strSQL = "select top " + str(@PageSize) + " " + @RetColumns + " from [" + @tblName + "]" + @strTmp + " " + @strOrder end end exec (@strSQL)