雇员表Employees:
字 段 称 类型 空否
EmployeeID Int,identity ,初值为1,增量为1 NOT NULL
Name VarChar(10) NOT NULL
Sex Char(2) NOT NULL
Birthdate Date NULL
Address Varchar(50) NULL
Phone Char(13) NULL
Re text
雇员工资表wage:
字段名 称 类型 空否
EmployeeID Int NOT NULL
Name VarChar(10) NOT NULL
Wage money NOT NULL
Putdate Date NOT NULL
工资税表tax:
字段名称 类型 空否
EmployeeID Int NOT NULL
Name VarChar(10) NOT NULL
Tax money NOT NULL
Paydate Date NOT NULL
*/ /*实践7:创建、执行、修改SQL Server数据库的存储过程*/
--(1) 编写存储过程,向雇员表Employees、雇员工资表wage和工资税表tax中各插入两条记录
go
create proc insert_emp@name varchar(10),
@sex char(2),
@brithdate datetime,
@address varchar(60),
@phone char(13),
@re textas
insert into employees (name,sex,brithdate,address,phone,re)
values(@name,@sex,@brithdate,@address,@phone,@re)
--insert into wage(employeeid,name,wage,putdate)
--insert into tax(employeeid,name,tax,putdate)exec insert_emp
@name='王斌',@sex='男',@brithdate=null,@address=null,@phone=null,@re=null这个存储过程有其他的方法写吗?
字 段 称 类型 空否
EmployeeID Int,identity ,初值为1,增量为1 NOT NULL
Name VarChar(10) NOT NULL
Sex Char(2) NOT NULL
Birthdate Date NULL
Address Varchar(50) NULL
Phone Char(13) NULL
Re text
雇员工资表wage:
字段名 称 类型 空否
EmployeeID Int NOT NULL
Name VarChar(10) NOT NULL
Wage money NOT NULL
Putdate Date NOT NULL
工资税表tax:
字段名称 类型 空否
EmployeeID Int NOT NULL
Name VarChar(10) NOT NULL
Tax money NOT NULL
Paydate Date NOT NULL
*/ /*实践7:创建、执行、修改SQL Server数据库的存储过程*/
--(1) 编写存储过程,向雇员表Employees、雇员工资表wage和工资税表tax中各插入两条记录
go
create proc insert_emp@name varchar(10),
@sex char(2),
@brithdate datetime,
@address varchar(60),
@phone char(13),
@re textas
insert into employees (name,sex,brithdate,address,phone,re)
values(@name,@sex,@brithdate,@address,@phone,@re)
--insert into wage(employeeid,name,wage,putdate)
--insert into tax(employeeid,name,tax,putdate)exec insert_emp
@name='王斌',@sex='男',@brithdate=null,@address=null,@phone=null,@re=null这个存储过程有其他的方法写吗?
在employee表上建立insert触发器,将插入的数据同时插入到wage和tax表中即可
在触发器中查看 inserted表。
create proc insert_emp
@name varchar(10),
@sex char(2),
@brithdate datetime,
@address varchar(60),
@phone char(13),
@re textas
begin
declare @EmployeeID int
insert into employees (name,sex,brithdate,address,phone,re)
select @name,@sex,@brithdate,@address,@phone,@re
if @@rowcount>0
begin
select @EmployeeID=SCOPE_IDENTITY()
insert into wage(employeeid,name,wage,putdate)
select @employeeid,@name,0,getdate()
insert into tax(employeeid,name,tax,putdate)
select @employeeid,@name,0,getdate()
endend
go
create proc insert_emp
@name varchar(10),
@sex char(2),
@brithdate datetime,
@address varchar(60),
@phone char(13),
@re text,
@putdate datetime,
@paydate datetime,
@wage money,
@tax moneyas
begin
declare @EmployeeID int
insert into employees (name,sex,brithdate,address,phone,re)
select @name,@sex,@brithdate,@address,@phone,@re
if @@rowcount>0
begin
select @EmployeeID=SCOPE_IDENTITY()
insert into wage(employeeid,name,wage,putdate)
select @employeeid,@name,@wage,@putdate
insert into tax(employeeid,name,tax,paydate)
select @employeeid,@name,@tax,@paydate
endend
这样写也是可以的 请各位在指导指导!