表之间数据交换与翻页存储过程

--------------从旧表创建新表 , 从一张表往另外一张表导入数据
------ 1. 创建表 #t1（从旧表创建新表）
select descText=Max(job_desc),min_lvl=Min(min_lvl),Max_lvl=Min(Max_lvl)  into #t1  from jobs group by job_desc
------ 2 . #t1 表往 JOBS 导入数据

insert into jobs(job_desc,min_lvl,Max_lvl) select descText,min_lvl,Max_lvl from #t1
------ 3 . 删除JOBS重复数据，删除 #t1
delete   jobs where job_id not in
(select Min(job_id) from jobs group by job_desc)
drop table #t1

 

------ 翻页存储过程 'doPage'

if exists (select * from sysobjects where id = object_id('doPage') and xtype='p')
drop proc doPage  ---有就删掉
go
create proc doPage
(
 @pageSize int,  --在这里声明变量不需要DECLARE
 @pageIndex int
)
as
set @pageIndex = @pageSize * @pageIndex
declare @sqlStr nvarchar(150)
set @sqlStr = 'select top '+
 convert(nvarchar(20),@pageSize)    ----convert 转换
+  ' *  from jobs where job_id not in (select top '+
  cast(@pageIndex as nvarchar(10))    ----cast 转换
+  '  job_id  from jobs) order by job_id'
exec (@sqlStr)   --- 需要括号,exec @sqlStr 这语句是调用存储过程

介绍一下和AspNetPager结合的不错的分页方案
http://www.cnblogs.com/sufei/archive/2009/08/11/1543530.html

转载于:https://www.cnblogs.com/jacd/archive/2009/08/17/1547811.html