日期:2013-11-08  浏览次数:20431 次


-- 获取指定页的数据CREATE PROCEDURE GetRecordFromPage    @tblName      varchar(255),       -- 表名    @fldName      varchar(255),       -- 字段名    @PageSize     int = 10,           -- 页尺寸    @PageIndex    int = 1,            -- 页码    @IsCount      bit = 0,            -- 前往记录总数, 非 0 值则前往    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序    @strWhere     varchar(1000) = '  -- 查询条件 (留意: 不要加 where)AS

declare @strSQL   varchar(6000)       -- 主语句declare @strTmp   varchar(100)        -- 临时变量declare @strOrder varchar(400)        -- 排序类型

if @OrderType != 0begin    set @strTmp = "<(select min"    set @strOrder = " order by [" + @fldName +"] desc"endelsebegin    set @strTmp = ">(select max"    set @strOrder = " order by [" + @fldName +"] asc"end

set @strSQL = "select top " + str(@PageSize) + " * from ["    + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["    + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["    + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"    + @strOrder

if @strWhere != '    set @strSQL = "select top " + str(@PageSize) + " * from ["        + @tblName + "] where [" + @fldName + "]" + @strTmp + "(["        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " "        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

if @PageIndex = 1begin    set @strTmp = ""    if @strWhere != '        set @strTmp = " where " + @strWhere

    set @strSQL = "select top " + str(@PageSize) + " * from ["        + @tblName + "]" + @strTmp + " " + @strOrderend

if @IsCount != 0    set @strSQL = "select count(*) as Total from [" + @tblName + "]"

exec (@strSQL)

GO