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

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

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

相关推荐