存储过程分页建立内存表的问题.

[ai_ni]

我写了一个内存表分页,为了有更好的拓展性，加了@table,@where等条件,分页的思路就是新建一个内存表,然后把@table的主键全下到里面,然后根据内存表@Temp的有序自增字段来控制@table的显示..问题来了,怎么样在存储过程中新建内存表,下面的存储过程执行成功，但调用的时候提示 必须声明变量 '@Temp'。

请各位帮我出出主意,分不够我再开帖奉上..谢谢了.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pagingShow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pagingShow]
GO
/**
内存表分页
*/
create proc dbo.pagingShow
--表名.
@table varchar(50),
--@table的主键.如Id
@id varchar(50),
--页容量
@PageSize INT = 20,
--当前页
@PageIndex INT = 1,
--是否计算记录总条数,为1则计算,并返回总条数;为0则不返回,继续分页操作.
@DoCount BIT = 0,
--查询条件.
@where varchar(255) = ''

AS
--组建SQL语句
declare @strSQL varchar(5000) -- 主语句
SET NOCOUNT ON
IF @DoCount = 1
BEGIN
IF @where = ''
BEGIN
set @strSQL = 'SELECT COUNT('+@id+') AS CT FROM ['+@table+']'
END
ELSE BEGIN
set @strSQL = 'SELECT COUNT('+@id+') AS CT FROM ['+@table+'] where '+ @where
END
exec(@strSQL)
END
ELSE BEGIN
DECLARE @PageLowerBound INT, @PageUpperBound INT
exec('declare @Temp table (tid INT identity(1,1) PRIMARY KEY, nid VARCHAR(20))')

SET @PageLowerBound = (@PageIndex - 1) * @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize
-----------------------------------------
SET ROWCOUNT @PageUpperBound
IF @where != ''
BEGIN
exec('INSERT INTO @Temp(nid) SELECT '+@id+' FROM ['+@table+'] Where '+@where)
-----------------------------------------
exec('SELECT a.* FROM ['+@table+'] a, @Temp t
WHERE (a.'+@id+' = t.nid)
AND (t.tid > '+@PageLowerBound+')
AND (t.tid <= '+@PageUpperBound+')')
END
ELSE BEGIN
exec('INSERT INTO @Temp (nid) SELECT '+@id+' FROM '+@table)
-----------------------------------------
exec('SELECT a.* FROM ['+@table+'] a, @Temp t
WHERE (a.'+@id+' = t.nid)
AND (t.tid > '+@PageLowerBound+')
AND (t.tid <= '+@PageUpperBound+')')
END
END
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[-狙击手-]

@temp 是局部变量，

用表吧
或全局临时表

[-狙击手-]

除非把所有的串一起exec

[ai_ni]

exec('declare @Temp table (tid INT identity(1,1) PRIMARY KEY, nid VARCHAR(20))')

@Temp 不是变量,是内存表..我用临时表#Temp已经测试成功了,但内存表效率要比临时表高..

不过楼上说的除非把所有的串一起exec也有道理..谢谢了.

[Lyw110]

动态SQL里面好像不能用变量表的，只能将声明变量表的语句也写入动态SQL字符串里才行

[ai_ni]

谢谢楼上的..期待牛人.

[-狙击手-]

@Temp 不是变量,是内存表..我用临时表#Temp已经测试成功了,但内存表效率要比临时表高..


－－－－


晕，我错了，内存表？？

这就是表变量

[ai_ni]

谢谢楼上的好意提醒，我孤陋寡闻了..
初步存储已经写出来了,测试100W条数据,成功,贴出来分享下.

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[pagingShow]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[pagingShow]
GO

CREATE PROC dbo.pagingShow
	--表名.
	@table VARCHAR(50),
	--@table的主键.如Id
	@id VARCHAR(50),
	--页容量
	@PageSize INT = 20,
	--当前页
	@PageIndex INT = 1,
	--是否计算记录总条数,为1则计算,并返回总条数;为0则不返回,继续分页操作.
	@DoCount BIT = 0,
	--查询条件.
	@where VARCHAR(255) = '',
	--排序.
	@order VARCHAR(255) = ''

AS
	--组建SQL语句
	DECLARE @strSQL varchar(5000) -- 主语句
	DECLARE @strSQL1 varchar(5000) -- 主语句	
	SET NOCOUNT ON
	IF @DoCount = 1
	BEGIN
		SET @strSQL = 'SELECT COUNT('+@id+') AS CT FROM ['+@table+']'
		--带where条件查询
		IF @where != ''
		BEGIN
		SET @strSQL = @strSQL + ' where '+@where
		END
	--计算总页数
	EXEC(@strSQL)
	END
	---------------------------------------------分页-----------------------------------------------
	ELSE BEGIN
		DECLARE @PageLowerBound VARCHAR(50), @PageUpperBound VARCHAR(50)
		SET @strSQL = 'DECLARE @Temp table (tid INT identity(1,1) PRIMARY KEY, nid INT)'

		SET @PageLowerBound = CAST(((@PageIndex - 1) * @PageSize) AS VARCHAR)
		SET @PageUpperBound = CAST((@PageLowerBound + @PageSize) AS VARCHAR)
		-----------------------------------------
		SET @strSQL = @strSQL + (' INSERT INTO @Temp(nid) SELECT top '+@PageUpperBound+' '+@id+' FROM ['+@table+']')
		--带where条件查询
		IF @where != ''
		BEGIN
			SET @strSQL = @strSQL + (' WHERE '+@where)
		END
		--order by 排序
		IF @order != ''
		BEGIN
			SET @strSQL = @strSQL + ' ORDER BY '+@order
		END
		SET @strSQL = @strSQL + (' SELECT a.* FROM ['+@table+'] a,@Temp t WHERE (a.'+@id+' = t.nid)  AND (t.tid > '+@PageLowerBound+') AND (t.tid <= '+@PageUpperBound+')')
		EXEC(@strSQL)
	END
	SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

[-狙击手-]

楼主好样的

[hbjlwhl]

楼主真的好强啊!!

[Limpire]

看了逻辑，效率应该不高：

DECLARE @Temp table (tid INT identity(1,1) PRIMARY KEY, nid INT)
这个 PRIMARY KEY 仅仅是主键约束，而没有索引，因为表变量是不支持索引的。

在页数不多的情况下，或许比临时表快，测试100W数据，试试找第5万页以上的看看。

用临时表的方法也不及邹建的方法快。

邹建的方法也仍有优化的地方。

[benbenkui]

老大的帖子在哪儿呢？

[JL99000]

愁死我了
我开始还想内存表是什么呢
。。。

[ai_ni]

结帖了..谢谢大家关注.

只是个想法,呵呵..