SqlServer存储过程

create database Companyha---创建公司数据库
use Companyha
create table Personner--员工表
(
    Id int identity primary key,
   Name varchar(20),---员工姓名
   Sex varchar(2),
   Age int ,---员工年龄
  Phone varchar(20),---联系方式
  birthday datetime,
)
select * from Personner
-------------------------------------------------------------------------------------------------------------------
--分页显示
go
if OBJECT_ID('P_Getfen','P') is not null--判断数据库是否已经存在此存储过程
drop procedure P_Getfen                  --有的话直接删除
go
create proc P_Getfen                        --创建存储过程
   @Name varchar(20),
   @Phone varchar(20),
   @pagesize int,
   @pageindex int,
   @rowcount int output
as
begin
set @rowcount=(select count(*) from Personner)
select top(@pagesize) *from (select ROW_NUMBER()over (order by Id)nid,*from Personner  where Name like '%'+@name+'%' 
or Phone like '%'+@Phone+'%') as temp where nid>(@pageindex-1)*@pagesize
end
go
declare @rowcount int
exec P_Getfen '','',5,1,@rowcount out--执行

-------------------------------------------------------------------------------------------------------------------
--添加
go
if OBJECT_ID('P_Addpresonner','P') is not null
drop procedure P_Addpresonner
go
create proc P_Addpresonner
   @Name varchar(20),---员工姓名
   @Sex varchar(2),
   @Age int ,---员工年龄
   @Phone varchar(20),---联系方式
   @birthday datetime
as
begin

insert into Personner values(@Name,@Sex,@Age,@Phone,@birthday)

end



exec P_Addpresonner '张三','男','20','13123773772','2015-10-08 20:28:13'




-------------------------------------------------------------------------------------------------------------------
---删除
go
if OBJECT_ID('P_delpresonner','P') is not null
drop procedure P_delpresonner
go
create proc P_delpresonner
   @Id int
as
begin
delete from Personner where Id=@Id
end
exec P_delpresonner 5


-------------------------------------------------------------------------------------------------------------------
---修改
go
if OBJECT_ID('P_uptpresonner','P') is not null
drop procedure P_uptpresonner
go
create proc P_uptpresonner
   @Id int,
   @Name varchar(20),---员工姓名
   @Sex varchar(2),
   @Age int ,---员工年龄
   @Phone varchar(20),---联系方式

   @birthday datetime

as

begin 
update Personner set Name=@Name,Sex=@Sex,Age=@Age,Phone=@Phone,Birthday=@birthday where Id=@Id
end
exec P_uptpresonner 1,'','',23,'','' 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭