sql数据库存储过程增删改查分页(多条件)

//建库
create database FMS
go
//使用
use FMS
go
//建表
create table Department   
(
Did                int primary key  identity,   
Dname              varchar(50),
Dnumale            varchar(50),
DestablishTime     varchar(50),
)
create table Clients
(
Cid         int primary key identity,
Cname       varchar(20),
Cage        varchar(11),
Csex        bit,
Cemail      varchar(50),
Did         int
)
//修改表字段类型
alter table clients alter column Cage int 
------------------------------------
go
//部门增
alter proc AddDepartment     
@Dname            varchar(50)   ,   
@Dnumale          varchar(50), 
@DestablishTime   varchar(50)
as 
begin
 insert into Department values(@Dname,@Dnumale,@DestablishTime)
end
exec  AddDepartment '人事部','S-303','2020-03-10'
go
//部门显
create proc ShowDepartmentClient
as
select s.Did 部门编号,s.Dname 部门名称,s.Dnumale 部门门牌号,s.DestablishTime 部门建立时间,a.Cage 员工年龄,a.Cemail 员工邮箱,a.Cname 员工姓名,a.Csex 员工性别
from Department s inner join Clients a
on s.Did=a.Did
exec ShowDepartmentClient
go
//部门删
create proc DeleteDepartmentClient
@id int
as
begin
 delete from Department where Did=@id
end
exec DeleteDepartmentClient 1
go
//部门改
create proc UpdateDepartment
@id            int,  
@name          varchar(20),   
@numale        varchar(20),   
@establishTime varchar(50)
as
begin
  update Department set Dname=@name,Dnumale=@numale,DestablishTime=@establishTime where Did=@id
end
exec UpdateDepartment 2,'研发部','S-310','2020-04-02'
go
//员工增
create proc AddClient     
@Cname       varchar(50)   ,   
@Cage        varchar(50), 
@Csex        varchar(50),
@Cemailas    varchar(50),
@Did         int
 as
 begin
 insert into Clients values(@Cname,@Cage,@Csex,@Cemailas,@Did)
end
exec AddClient 'zll','20',1,'zll@163.com',1
go
//员工显
create proc ShowClients
as
select a.Cage 员工年龄,a.Cemail 员工邮箱,a.Cname 员工姓名,a.Csex 员工性别
from Clients a 
exec ShowClients
go
//员工删
create proc DeleteClient
@id int
as
begin
 delete from Clients where Cid=@id
end
exec DeleteClient 1
go
//员工改
alter proc UpdateClient
@Cid      int,
@name     varchar(50),  
@age      int,   
@sex      bit,   
@emailas  varchar(50),
@Did      int   
as
begin
  update Clients set Cname=@name,Cage=@age,Csex=@sex,Cemail=@emailas,Did=@Did where Cid=@Cid
end
exec UpdateClient 2,'whg',25,0,'whg@163.com',2
go
//分页多条件查询
create proc MyPaging3
(
@pageIndex int,
@pageSize int,
@Name varchar(50),
@price decimal,
@totalRecordCount int output
)
as
begin
if len(@Name)=0 and @price =0
begin
select * into #Temp1 from Clients
select top (@pageSize) * from #Temp1 where Cid not in (select top (@pageSize * (@PageIndex-1)) Cid from #Temp1)
select @totalRecordCount = Count(5) from #Temp1
end
else if len(@Name)=0 and @price>0
begin
select * into #Temp2 from Clients where Cid>@price
select top (@pageSize) * from #Temp2 where Cid not in (select top (@pageSize * (@PageIndex-1)) CId from #Temp2)
select @totalRecordCount = Count(5) from #Temp2
end
else if len(@Name)>0 and @price=0
begin
select * into #Temp3 from Clients where Cname like ('%'+@Name+'%')
select top (@pageSize) * from #Temp3 where Cid not in (select top (@pageSize * (@PageIndex-1)) Cid from #Temp3)
select @totalRecordCount = Count(5) from #Temp3
end
else
begin
select * into #Temp4 from Clients where Cname like ('%'+@Name+'%') and Cid>@price
select top (@pageSize) * from #Temp4 where Cid not in (select top (@pageSize * (@PageIndex-1)) Cid from #Temp4)
select @totalRecordCount = Count(5) from #Temp4
end
end
//调用分页
declare @a int
exec MyPaging3 1,2,'hyx',1,@a output
select @a
select * from Clients