给个分页的存储过程给你,不过没有建临时表的过程。可以参考下分页的代码:
CREATE PROCEDURE Search
(
@TableName nvarchar(1000),
@SearchContent nvarchar(1000),
@WhereString nvarchar(1000),
@OrderString nvarchar(1000),
@PageSize int,
@PageIndex int,
@PageCount int output,
@RecordCount int output
)
ASdeclare @SQL nvarchar(1000)
declare @str nvarchar(1000)set @str=N'select @RecordCount=count(*) from '+@TableName+' '+@WhereString
exec sp_executesql @str,
N'@RecordCount int output',
@RecordCount outputset @PageCount=ceiling(@RecordCount*1.0/@PageSize)if @PageIndex = 0 or @PageCount<=1
begin
set @SQL='select top '+str(@PageSize)+' '+@SearchContent+' from '+@TableName+' '+@WhereString+' '+@OrderString+' desc '
end
else
begin
if @PageIndex = @PageCount -1
begin
set @SQL='select '+@SearchContent+' from ( select top '+str(@RecordCount - @PageSize * @PageIndex)+' * from '+@TableName+' '+@WhereString+' '+@OrderString+' asc ) TempTable '+@OrderString+' desc '
end
else
begin
set @SQL='select top '+str(@PageSize) +' '+@SearchContent+' from ( select top '+str(@RecordCount - @PageSize * @PageIndex)+' * from '+@TableName+' '+@WhereString+' '+@OrderString+' asc ) TempTable '+@OrderString+' desc '
end
endexec(@SQL)
GO
CREATE PROCEDURE Search
(
@TableName nvarchar(1000),
@SearchContent nvarchar(1000),
@WhereString nvarchar(1000),
@OrderString nvarchar(1000),
@PageSize int,
@PageIndex int,
@PageCount int output,
@RecordCount int output
)
ASdeclare @SQL nvarchar(1000)
declare @str nvarchar(1000)set @str=N'select @RecordCount=count(*) from '+@TableName+' '+@WhereString
exec sp_executesql @str,
N'@RecordCount int output',
@RecordCount outputset @PageCount=ceiling(@RecordCount*1.0/@PageSize)if @PageIndex = 0 or @PageCount<=1
begin
set @SQL='select top '+str(@PageSize)+' '+@SearchContent+' from '+@TableName+' '+@WhereString+' '+@OrderString+' desc '
end
else
begin
if @PageIndex = @PageCount -1
begin
set @SQL='select '+@SearchContent+' from ( select top '+str(@RecordCount - @PageSize * @PageIndex)+' * from '+@TableName+' '+@WhereString+' '+@OrderString+' asc ) TempTable '+@OrderString+' desc '
end
else
begin
set @SQL='select top '+str(@PageSize) +' '+@SearchContent+' from ( select top '+str(@RecordCount - @PageSize * @PageIndex)+' * from '+@TableName+' '+@WhereString+' '+@OrderString+' asc ) TempTable '+@OrderString+' desc '
end
endexec(@SQL)
GO
CREATE proc getdataset
@TableList Varchar(200)='*',--搜索表的字段,比如:’id,datatime,job‘,用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500)='',--搜索条件,这里不用写where,比如:job=’teacher‘and class='2'
@SelectOrderId Varchar(20),--表主键字段名。比如:id
@SelectOrder Varchar(200)='', --排序,可以使用多字段排序但主键字段必需在最前面.也可以不写,比如:order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT --总记录数(存储过程输出参数)
as
declare @TmpSelect NVarchar(600)
declare @Tmp NVarchar(600) set nocount on--关闭计数set @TmpSelect = 'select @RecordCount = count(*) from '+@TableName+' '+@SelectWhereexecute sp_executesql
@TmpSelect, --执行上面的sql语句
N'@RecordCount int OUTPUT' , --执行输出数据的sql语句,output出总记录数
@RecordCount OUTPUT if (@RecordCount = 0) --如果没有贴子,则返回零
return 0
/*判断页数是否正确*/
if (@intPageNo - 1) * @intPageSize > @RecordCount --页号大于总页数,返回错误
return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectWhere +' '+@SelectOrder+') and '+@SelectWhere +' '+@SelectOrder
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' '+@TableList+' from '+@TableName+' where '+@SelectOrderId+' not in(select top '+str((@intPageNo-1)*@intPageSize)+' '+@SelectOrderId+' from '+@TableName+' '+@SelectOrder+') '+@SelectOrder
end
execute sp_executesql @TmpSelect
return(@@rowcount)
GO