sql server分页存储过程
利用表变量实现分页一、
[*]代码
[*]
[*] CREATE PROCEDURE .
[*] @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 .
[*] @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> FROM publish))
id 为publish 表的关键字
------------------------
分页方案：
select top 页大小 *
from table1
where> (select max (id) from
(select top ((页码-1)*页大小)> )
order by> 全部的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 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
页:
[1]