分页查询:思路,查询符合条件的记录,加一个自动编号字段,然后返回目标页,删除临时表:----------------------------------------------------
--Explain:通用分页查询(临时表方式)
--Author:Manqing yu
--Date:2008-04-10
----------------------------------------------------
CREATE PROCEDURE PageSelect_TempTable
@strField nvarchar(4000), -- 查询字段,语句(没有SELECT关键字)
@strTableName nvarchar(500), --表名,视图名(多个加,)
@strWhere nvarchar(4000), --查询条件(包括Where)
@strOrder nvarchar(500), --排序语句(可包括Group By)
@PageSize int, -- 每页的大小(行数)
@PageCurrent int --要查询的页
AS
DECLARE @strTop varchar(8000)--查询语句
DECLARE @strInsert varchar(8000)
DECLARE @strSelect varchar(8000)
DECLARE @strDel varchar(200)
DECLARE @strTempTable nvarchar(150) --生成的临时表名(tmpTable+时间)
set @strTempTable='#tmptable'+
rtrim(cast(datepart(year,getdate())as char))+rtrim(cast(datepart(month,getdate())as char))
+rtrim(cast(datepart(day,getdate())as char))+rtrim(cast(datepart(hour,getdate())as char))
+rtrim(cast(datepart(minute,getdate())as char))+rtrim(cast(datepart(second,getdate())as char))
+rtrim(cast(datepart(millisecond,getdate())as char))set @strTempTable=rtrim(@strTempTable)
-- 如果显示第一页,可以直接用 top 来完成
IF @PageCurrent = 1
BEGIN
set @strTop=
'SELECT TOP ' + CAST(@PageSize as varchar(20))+rtrim(@strField)+' FROM '+rtrim(@strTableName)+ rtrim(@strWhere)+rtrim(@strOrder)
EXEC(@strTop)
RETURN
ENDif exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[(@strTempTable)]'))
drop table [tempdb].[dbo].[@strTempTable]--查询符合条件的记录生成临时表
set @strInsert=
'SELECT IDENTITY(INT,1,1) AS IndexID, '+rtrim(@strField)+
' INTO '+@strTempTable+' FROM '+rtrim(@strTableName)+rtrim(@strWhere)+rtrim(@strOrder)
EXEC(@strInsert)--返回目标页的记录
set @strSelect=
'SELECT * FROM '+@strTempTable+' WHERE
IndexID>'+rtrim(cast((@PageCurrent-1)*@PageSize as char))+
' AND IndexID<='+rtrim(cast(@PageCurrent*@PageSize as char))
EXEC(@strSelect)--删除临时表
set @strDel='
DROP TABLE '+ @strTempTable
EXEC(@strDel)
GO
--调用
exec PageSelect_TempTable ' ccode,cname',' CU_CustomerBasic',' WHERE 1=1',' Order by cName',5,3
随便指向数据库中的一个表即可,在执行时,临时表创建完毕,查询临时表的时候总是找不到,调试了一个上午,真快糊涂了,帮下忙,谢谢,在线等
--Explain:通用分页查询(临时表方式)
--Author:Manqing yu
--Date:2008-04-10
----------------------------------------------------
CREATE PROCEDURE PageSelect_TempTable
@strField nvarchar(4000), -- 查询字段,语句(没有SELECT关键字)
@strTableName nvarchar(500), --表名,视图名(多个加,)
@strWhere nvarchar(4000), --查询条件(包括Where)
@strOrder nvarchar(500), --排序语句(可包括Group By)
@PageSize int, -- 每页的大小(行数)
@PageCurrent int --要查询的页
AS
DECLARE @strTop varchar(8000)--查询语句
DECLARE @strInsert varchar(8000)
DECLARE @strSelect varchar(8000)
DECLARE @strDel varchar(200)
DECLARE @strTempTable nvarchar(150) --生成的临时表名(tmpTable+时间)
set @strTempTable='#tmptable'+
rtrim(cast(datepart(year,getdate())as char))+rtrim(cast(datepart(month,getdate())as char))
+rtrim(cast(datepart(day,getdate())as char))+rtrim(cast(datepart(hour,getdate())as char))
+rtrim(cast(datepart(minute,getdate())as char))+rtrim(cast(datepart(second,getdate())as char))
+rtrim(cast(datepart(millisecond,getdate())as char))set @strTempTable=rtrim(@strTempTable)
-- 如果显示第一页,可以直接用 top 来完成
IF @PageCurrent = 1
BEGIN
set @strTop=
'SELECT TOP ' + CAST(@PageSize as varchar(20))+rtrim(@strField)+' FROM '+rtrim(@strTableName)+ rtrim(@strWhere)+rtrim(@strOrder)
EXEC(@strTop)
RETURN
ENDif exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[(@strTempTable)]'))
drop table [tempdb].[dbo].[@strTempTable]--查询符合条件的记录生成临时表
set @strInsert=
'SELECT IDENTITY(INT,1,1) AS IndexID, '+rtrim(@strField)+
' INTO '+@strTempTable+' FROM '+rtrim(@strTableName)+rtrim(@strWhere)+rtrim(@strOrder)
EXEC(@strInsert)--返回目标页的记录
set @strSelect=
'SELECT * FROM '+@strTempTable+' WHERE
IndexID>'+rtrim(cast((@PageCurrent-1)*@PageSize as char))+
' AND IndexID<='+rtrim(cast(@PageCurrent*@PageSize as char))
EXEC(@strSelect)--删除临时表
set @strDel='
DROP TABLE '+ @strTempTable
EXEC(@strDel)
GO
--调用
exec PageSelect_TempTable ' ccode,cname',' CU_CustomerBasic',' WHERE 1=1',' Order by cName',5,3
随便指向数据库中的一个表即可,在执行时,临时表创建完毕,查询临时表的时候总是找不到,调试了一个上午,真快糊涂了,帮下忙,谢谢,在线等
你不要这样select * into 创建临时表用这个
create table #temp看看怎么样,那样你当然找不到了,如果能找的到,sql server不就麻烦了
set @strTempTable='#tmptable'+
rtrim(cast(datepart(year,getdate())as char))+rtrim(cast(datepart(month,getdate())as char))
+rtrim(cast(datepart(day,getdate())as char))+rtrim(cast(datepart(hour,getdate())as char))
+rtrim(cast(datepart(minute,getdate())as char))+rtrim(cast(datepart(second,getdate())as char))
+rtrim(cast(datepart(millisecond,getdate())as char))
改成:
set @strTempTable='##tmptable'+
rtrim(cast(datepart(year,getdate())as char))+rtrim(cast(datepart(month,getdate())as char))
+rtrim(cast(datepart(day,getdate())as char))+rtrim(cast(datepart(hour,getdate())as char))
+rtrim(cast(datepart(minute,getdate())as char))+rtrim(cast(datepart(second,getdate())as char))
+rtrim(cast(datepart(millisecond,getdate())as char))
1、临时表在动态SQL里不要采用select into 方式,应该先创建Create table #(f1 char(10),.....),然后insert into select 写入数据即可2、采用全局临时表建议用第一种,先创建临时表
因为局部临时表有生命周期的问题.动态语句里创建的局部临时表,在动态语句结束后,生命周期结束,就不能访问了.
所以方法如楼上所述.
先在动态语句外部显式建立局部临时表,创建identity列,然后再在动态语句中使用 insert select 插入数据.而采用全局临时表或实表的方式不可行, 并发时,数据会取错.不建议
CREATE PROCEDURE PageSelect_TempTable
@strField nvarchar(4000), -- 查询字段,语句(没有SELECT关键字)
@strTableName nvarchar(500), --表名,视图名(多个加,)
@strWhere nvarchar(4000), --查询条件(包括Where)
@strOrder nvarchar(500), --排序语句(可包括Group By)
@PageSize int, -- 每页的大小(行数)
@PageCurrent int --要查询的页
AS
begin
declare @b int,@e int
declare @sql varchar(8000)
select @b = (@PageCurrent -1)*@PageSize+1,@e = @PageCurrent*@PageSize
select @sql = 'select top '+rtrim(@e)+' identity(int,1,1) as pxid,'+ @strField
+' into # from '+@strTableName+' '+@strWhere+' '+@strOrder
+' select * from # where pxid between '+rtrim(@b)+' and '+rtrim(@e)
exec(@sql)
end
通用的分页查询存储过程网上有很多,我随便贴一个:
ALTER procedure [dbo].[Pagination](
@pagesize int, -- 页大小
@pageindex int, -- 当前页
@identity varchar(100), -- 唯一列
@cells varchar(1000)='*', -- 显示列
@tables varchar(1000), -- 表名称
@condition varchar(2000)=NULL, -- 查询条件
@top int = -1, --
@orderby varchar(200)=NULL -- 排序 desc
)
as
set nocount on
declare @rowcount int;
declare @SQL nvarchar(4000); set @SQL = N'select @rowcount = count(0) from ' + @tables + ' where 1=1 ';
if(@condition IS NOT NULL) set @SQL = @SQL + @condition;
exec sp_executesql @SQL,N'@rowcount int output',@rowcount output; if(@top != -1 and @rowcount > @top) set @rowcount = @top; set @SQL = N'declare @___lb int;';
set @SQL = @SQL + N'declare @___ub int;';
set @SQL = @SQL + N'set @___lb=(@pageindex-1)*@pagesize;';
set @SQL = @SQL + N'set @___ub=@___lb+@pagesize;';
set @SQL = @SQL + N'set ROWCOUNT @___ub;'; set @SQL = @SQL + N'with ___t as (select *'; if(@orderby is not null and @orderby != '')
set @SQL = @SQL + N',row_number() over(order by ' + @orderby + ',' + @identity + ' asc) as ___p from (';
else
set @SQL = @SQL + N',row_number() over(order by ' + @identity + ' asc) as ___p from ('; if(@top != -1)
set @SQL = @SQL + N'select top ' + cast(@top as nvarchar(10)) + ' ';
else
set @SQL = @SQL + N'select '; if(@cells IS NOT NULL)
set @SQL = @SQL + @cells;
else
set @SQL = @SQL + N'*'; set @SQL = @SQL + ' from ' + @tables + ' where 1=1'; if(@condition IS NOT NULL and @condition != '') set @SQL = @SQL + ' ' + @condition; set @SQL = @SQL + ') as ___q) select * from ___t where ___p>@___lb and ___p<=@___ub;'; exec sp_executesql @SQL,N'@pagesize int,@pageindex int',@pagesize = @pagesize,@pageindex = @pageindex; return @rowcount;
set nocount off
简单的不好吗?
2.字段可以不固定,如11楼所写的,你在@strField里面动态指定就可以了.