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
