SQL2005三种存储过程分页方式以及插入10万条数据

     if exists (select * from sys.databases where name='test')
   drop database test
   create database test
go
    use test
   go
     /----创建  员工信息表(employeeInfo)

    if exists(select * from sys.tables where name='employeeInfo' )
  drop table employeeInfo
  create table employeeInfo(
      empId int identity(1,1) primary key,
      empname varchar(50),
      sex nvarchar(2),
      age int 
)
go
select * from employeeInfo
go
/---创建十万条测试数据  插入数据存储过程
    if exists(select * from sys.procedures where name='proDataInsertIntoEmployeeInfo')

  drop procedure proDataInsertIntoEmployeeInfo
go
  create procedure proDataInsertIntoEmployeeInfo
  as
  declare @dataCount int ,@i int
  select @dataCount=100000,@i=1
  while(@i<=@dataCount)
   begin
  insert into employeeInfo values((char (rand()*100))+(char (rand()*100))+(char (rand()*100)),'男',rand()*50)
   set @i=@i+1
  end
go
execute  proDataInsertIntoEmployeeInfo
  
      /--产生随字符
select (char (rand()*100))+(char (rand()*100))+(char (rand()*100))
/--产生随即1-50的数字
select rand()*50
select * from employeeInfo
go

  /--创建得到 员工信息表总记录数存储过程selectEmployeeInfoCount
  if exists(select * from sys.procedures where name='selectEmployeeInfoCount')
drop  procedure selectEmployeeInfoCount
go
  create procedure selectEmployeeInfoCount
  as
   declare @EmployeeCount int
   select @EmployeeCount=count(*) from employeeInfo
   return @EmployeeCount
go
execute selectEmployeeInfoCount


   

/---创建员工信息分页  存储过程 (not  in ) 存储过程

   if exists(select * from sys.procedures where name='EmployeeInfoPage')
drop procedure EmployeeInfoPage
go
   create procedure EmployeeInfoPage
   @startRowIndex  int=1 ,
   @pageSize int=10
  as
 
    select top (@pageSize) * from employeeInfo where empId not in
   (select top (@pageSize*(@startRowIndex-1)) empId from employeeInfo  order by empId )
  order by empId
go
 
  execute EmployeeInfoPage  10000,10
go
   /--创建 排序储存过程
    if exists(select * from sys.procedures where name='EmployeeInfoPageByDesc')
  drop  procedure EmployeeInfoPageByDesc

  /--创建存储过程（EmployeeInfoPageByDesc）
create procedure EmployeeInfoPageByDesc
     @startRowIndex  int=1 ,
     @pageSize int=10
     as
     declare   @recordCount int
     execute @recordCount=selectEmployeeInfoCount
     select top(@pageSize) * from
    (select top (@recordCount-(@startRowIndex-1)*@pageSize) * from employeeInfo order by empId desc)
     emp
     order by empId asc
go