请各位帮我出出主意,分不够我再开帖奉上..谢谢了.
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
