Question

sql存储过程分页 临时表问题

sql使用存储过程分页，用临时表记录ID方式，但是经常会遇到临时表无法删除的问题，该怎么处理？

另外还想问下，sql存储过程分页临时表的方法，若同时有2个人进行分页，那不是要出错》

Answer 1

sql分页不一定用存储过程,就过个人认为存储过程尽量少用!
--前提是必需有一列是自动增长类型,唯一性

--方法一

SELECT DISTINCT TOP 8 CategoryID
FROM tbl_Product_Products
WHERE (UserID = 73) AND (CategoryID >
(SELECT MAX(categoryid)
FROM (SELECT DISTINCT TOP 16 categoryid
FROM tbl_product_products where userid=73
ORDER BY categoryid) AS b))
ORDER BY CategoryID

--方法二

select top 10 * from [order details]
where orderid>all(select top 10 orderid from [order details] order by orderid)
order by orderid
下面的来自CSDN-------------------------------------
SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

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

分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID

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

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID

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

分页方案三：(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用

在实际情况中，要具体分析。

更多的讨论见：
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515

Answer 2

直接创建这个存储过程就可以用，里面有注释，我自己就在用，效率很高。上百万的数据，结合AJAX使用的话，翻页时间都不到1秒。

/*

WEB高效率分页输出存储过程 版本2

返回两个 记录集，第一个为记录数，分页数，第二个为记录列表

注意：需要查询的表必须有一列是自动增加的标示列。如果排序字段不是该列，需要指定标识列名称以及排序

*/

--本版本增加了标示列名称和排序规则两个参数,取消了@doCount参数

CREATE procedure dbo.tt_PageOut2

@dbOwner varchar(50)='dbo',
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
--@doCount bit = 0, -- 无效参数
@OrderType bit = 0, -- 设置排序类型, 1降序 0升序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)

,@idName varchar(100)='' --标示列名称
,@idOrder bit=0 --标示列排序 1降序 0升序

AS

set nocount on

if @strWhere='' or @strWhere is null set @strWhere=' 1=1 '
if @idName=@fldName set @idName=''

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

--以下执行总数统计,计算总数，页数

set @strSQL = 'select count(*) as Total,ceiling(convert(decimal,count(*))/'+convert(varchar(10),@PageSize)+') as PageCount from '+@dbOwner+'.' + @tblName + ' where '+@strWhere
exec (@strSql)

--以下计算返回结果集

set @strSql=''

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 @idName<>'' begin
if @idOrder!=0
set @strOrder=@strOrder+', ['+@idName+'] desc'
else
set @strOrder=@strOrder+', ['+@idName+'] asc'
end

if @PageIndex = 1 begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+@dbOwner+'.' + @tblName + ' where ' + @strWhere + ' ' + @strOrder

--如果是第一页就执行以上代码，这样会加快执行速度

end else begin

--以下代码赋予了@strSQL以真正执行的SQL代码

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

end else begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @idName + '] not in '
+ '(select ['+ @idName + '] from (select top '
+ str((@PageIndex-1)*@PageSize) + ' ['+ @idName + '] from ['+@dbOwner+'].['
+ @tblName + '] where '+@strWhere+' '+ @strOrder + ') as tblTmp) and '+@strWhere+' '+ @strOrder

end

end

--print @strSQL
exec (@strSQL)

set nocount off