sql server分页存储过程

利用表变量实现分页

一、

 

代码  
 
 CREATE PROCEDURE [dbo].[GetRecordFromPage]   
    @SelectList            VARCHAR(2000),    --欲选择字段列表  
    @TableSource        VARCHAR(100),    --表名或视图表   
    @SearchCondition    VARCHAR(2000),    --查询条件  
    @OrderExpression    VARCHAR(1000),    --排序表达式  
    @PageIndex            INT = 1,        --页号,从0开始  
    @PageSize            INT = 10        --页尺寸  
AS   
BEGIN  
    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''  
    BEGIN  
        SET @SelectList = '*' 
    END  
    PRINT @SelectList  
      
    SET @SearchCondition = ISNULL(@SearchCondition,'')  
    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))  
    IF @SearchCondition <> ''  
    BEGIN  
        IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'  
        BEGIN  
            SET @SearchCondition = 'WHERE ' + @SearchCondition  
        END  
    END  
    PRINT @SearchCondition  
 
    SET @OrderExpression = ISNULL(@OrderExpression,'')  
    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))  
    IF @OrderExpression <> ''  
    BEGIN  
        IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'  
        BEGIN  
            SET @OrderExpression = 'ORDER BY ' + @OrderExpression  
        END  
    END  
    PRINT @OrderExpression  
 
    IF @PageIndex IS NULL OR @PageIndex < 1 
    BEGIN  
        SET @PageIndex = 1 
    END  
    PRINT @PageIndex  
    IF @PageSize IS NULL OR @PageSize < 1 
    BEGIN  
        SET @PageSize = 10 
    END  
    PRINT  @PageSize  
 
    DECLARE @SqlQuery VARCHAR(4000)  
 
    SET @SqlQuery='SELECT '+@SelectList+',RowNumber   
    FROM   
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber   
          FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource   
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)   
    + ' AND ' +   
    CAST((@PageIndex * @PageSize) AS VARCHAR)   
    PRINT @SqlQuery  
    SET NOCOUNT ON  
    EXECUTE(@SqlQuery)  
    SET NOCOUNT OFF  
   
    RETURN @@RowCount  
END 

二

 

代码  
 
 CREATE PROCEDURE [dbo].[GetRecordFromPage]   
    @SelectList            VARCHAR(2000),    --欲选择字段列表  
    @TableSource        VARCHAR(100),    --表名或视图表   
    @SearchCondition    VARCHAR(2000),    --查询条件  
    @OrderExpression    VARCHAR(1000),    --排序表达式  
    @PageIndex            INT = 1,        --页号,从0开始  
    @PageSize            INT = 10        --页尺寸  
AS   
BEGIN  
    IF @SelectList IS NULL OR LTRIM(RTRIM(@SelectList)) = ''  
    BEGIN  
        SET @SelectList = '*' 
    END  
    PRINT @SelectList  
      
    SET @SearchCondition = ISNULL(@SearchCondition,'')  
    SET @SearchCondition = LTRIM(RTRIM(@SearchCondition))  
    IF @SearchCondition <> ''  
    BEGIN  
        IF UPPER(SUBSTRING(@SearchCondition,1,5)) <> 'WHERE'  
        BEGIN  
            SET @SearchCondition = 'WHERE ' + @SearchCondition  
        END  
    END  
    PRINT @SearchCondition  
 
    SET @OrderExpression = ISNULL(@OrderExpression,'')  
    SET @OrderExpression = LTRIM(RTRIM(@OrderExpression))  
    IF @OrderExpression <> ''  
    BEGIN  
        IF UPPER(SUBSTRING(@OrderExpression,1,5)) <> 'WHERE'  
        BEGIN  
            SET @OrderExpression = 'ORDER BY ' + @OrderExpression  
        END  
    END  
    PRINT @OrderExpression  
 
    IF @PageIndex IS NULL OR @PageIndex < 1 
    BEGIN  
        SET @PageIndex = 1 
    END  
    PRINT @PageIndex  
    IF @PageSize IS NULL OR @PageSize < 1 
    BEGIN  
        SET @PageSize = 10 
    END  
    PRINT  @PageSize  
 
    DECLARE @SqlQuery VARCHAR(4000)  
 
    SET @SqlQuery='SELECT '+@SelectList+',RowNumber   
    FROM   
        (SELECT ' + @SelectList + ',ROW_NUMBER() OVER( '+ @OrderExpression +') AS RowNumber   
          FROM '+@TableSource+' '+ @SearchCondition +') AS RowNumberTableSource   
    WHERE RowNumber BETWEEN ' + CAST(((@PageIndex - 1)* @PageSize+1) AS VARCHAR)   
    + ' AND ' +   
    CAST((@PageIndex * @PageSize) AS VARCHAR)   
    PRINT @SqlQuery  
    SET NOCOUNT ON  
    EXECUTE(@SqlQuery)  
    SET NOCOUNT OFF  
   
    RETURN @@RowCount  
END 

三、

从数据表中取出第n条到第m条的记录的方法

从publish 表中取出第 n 条到第 m 条的记录：

SELECT TOP m-n+1 *

FROM publish

WHERE (id NOT IN

　　　　(SELECT TOP n-1 id

　　　　 FROM publish))

 

id 为publish 表的关键字

------------------------

分页方案：

select top 页大小 *

from table1

where id>

(select max (id) from

(select top ((页码-1)*页大小) id from table1 order by id) as T

)

order by id

 

 

全部的sql语句：

 

代码  
 
--获取指定页的数据：  
CREATE PROCEDURE pagination3  
@tblName varchar(255), -- 表名  
@strGetFields varchar(1000) = ''*'', -- 需要返回的列   
@fldName varchar(255)='''', -- 排序的字段名  
@PageSize int = 10, -- 页尺寸  
@PageIndex int = 1, -- 页码  
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回  
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序  
@strWhere varchar(1500) = '''' -- 查询条件 (注意: 不要加 where)  
AS  
 
declare @strSQL varchar(5000) -- 主语句  
declare @strTmp varchar(110) -- 临时变量  
declare @strOrder varchar(400) -- 排序类型  
 
if @doCount != 0  
begin  
if @strWhere !=''''  
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere  
else  
set @strSQL = "select count(*) as Total from [" + @tblName + "]"  
end  
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：  
else  
begin  
if @OrderType != 0  
begin  
set @strTmp = "<(select min" 
set @strOrder = " order by [" + @fldName +"] desc"  
--如果@OrderType不是0，就执行降序，这句很重要！  
end  
else  
begin  
set @strTmp = ">(select max" 
set @strOrder = " order by [" + @fldName +"] asc"  
end  
 
if @PageIndex = 1 
begin  
if @strWhere != ''''   
 
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  
　　　　　　　　from [" + @tblName + "] where " + @strWhere + " " + @strOrder  
else  
 
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "   
　　　　　　　　from ["+ @tblName + "] "+ @strOrder  
--如果是第一页就执行以上代码，这样会加快执行速度  
end  
else  
begin  
--以下代码赋予了@strSQL以真正执行的SQL代码   
　  
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " 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) +" "+@strGetFields+ " from ["  
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["  
+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["  
+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "  
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder  
end   
 
end   
 
exec (@strSQL)  
 
GO