求SQL高手写一个存储过程，多谢！

Question

数据库里有两个表：员工表Employee和部门表Department。部门表Department的结构和数据如下：| DeptID | DeptName |
|--------+----------|
|    1   | Office   |
|--------+----------|
|    2   | HR       |
|--------+----------|
|    3   | IT       |
|--------+----------|
|    4   | Sales    |
|--------+----------|
|    5   | Other    |
|--------+----------|有三条数据：
| EmployeeID | FirstName | LastName | DepartmentID | Scores |
|------------+-----------+----------+--------------+--------|
|   15       | first15   | last15   |    2         |  80    |
|------------+-----------+----------+--------------+--------|
|   16       | first16   | last16   |    4         |  92    |
|------------+-----------+----------+--------------+--------|
|   17       | first17   | last17   |    5         |  75    |
|------------+-----------+----------+--------------+--------|请写一个存储过程InsertRecords将这三条记录加入到员工表Employee中，并使用事务（transaction）检查DepartmentID是否已存在部门表Department中。一旦发现有不存在的情况，取消所有的插入操作。（注：这是一道面试题，也没说两个表有无外键关联。是不是根据有无关联应该有两种写法？）

Answer 1

DepartmentID这不是关联部门表的id吗？

Answer 2

DepartmentID 是设置为外键的话正常插入就好了
begin try
begin tran
 insert into Employee
  select 15 | first15 | last15 | 2 | 80 |
  union
  sleect ...
  ...
  commit
end tran
end try
begin catch
  rollback
end

Answer 3

员工表里的DepartmentID确实是对应部门表里的DeptID，但是题目里没说是否关联。这是不是应该写两个SP，怎样写？多谢！

Answer 4

create proc procnameas
begininsert into employee values(...)
insert into employee values(...)
insert into employee values(...)end
--事务不会

Answer 5

您说得在理。但我平时写增删改查还可以，从来没写过SP，也没用过Transaction。不知从哪里下手。

Answer 6

事务结束后才会成交.  if not exit()  goto ERR:rollback

Answer 7

use tempdb
GO
Set nocount On if object_id('Department') Is Not Null
Drop Table Department
if object_id('Employee') Is Not Null
Drop Table Employee  
Create Table Department(DeptID int primary key,DeptName nvarchar(50))
Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)go
Insert Into Department(DeptID,DeptName) values(1,'Office')
Insert Into Department(DeptID,DeptName) values(2,'HR')
Insert Into Department(DeptID,DeptName) values(3,'IT')
Insert Into Department(DeptID,DeptName) values(4,'Sales')
Insert Into Department(DeptID,DeptName) values(5,'Other')
go--存儲過程
if object_id('InsertRecords') Is Not Null
Drop proc InsertRecords
Go
Create proc InsertRecords
(
@EmployeeID int,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@DepartmentID int,
@Scores int
)  
As
If Exists(Select 1 From Department Where DeptID=@DepartmentID)
Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) values(@EmployeeID,@FirstName,@LastName,@DepartmentID,@Scores)
GO--存儲過程調用begin tran Exec InsertRecords @EmployeeID=15,@FirstName='first15',@LastName='last15',@DepartmentID=2,@Scores=80 
If @@ROWCOUNT =0 Goto ErrFlagExec InsertRecords @EmployeeID=16,@FirstName='first16',@LastName='last16',@DepartmentID=4,@Scores=92 
If @@ROWCOUNT =0 Goto ErrFlagExec InsertRecords @EmployeeID=17,@FirstName='first17',@LastName='last17',@DepartmentID=5,@Scores=75 
If @@ROWCOUNT =0 Goto ErrFlagCommit Tran
Print N'Commit Tran'
Goto ExitFlagErrFlag:
Rollback Tran
Print N'Rollback Tran'ExitFlag:Go

Answer 8

我在SQL Server 2008里边试了一下，很好用！多谢。原题要求所有的插入操作以及整个操作的Transaction都要写在一个存储过程里，请问这好实现吗？再次感谢！

Answer 9

use tempdb
GO
Set nocount On if object_id('Department') Is Not Null
    Drop Table Department
if object_id('Employee') Is Not Null
    Drop Table Employee     
Create Table Department(DeptID int primary key,DeptName nvarchar(50))
Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)go
Insert Into Department(DeptID,DeptName) values(1,'Office')
Insert Into Department(DeptID,DeptName) values(2,'HR')
Insert Into Department(DeptID,DeptName) values(3,'IT')
Insert Into Department(DeptID,DeptName) values(4,'Sales')
Insert Into Department(DeptID,DeptName) values(5,'Other')
go--存儲過程
if object_id('InsertRecords') Is Not Null
    Drop proc InsertRecords
Go
Create proc InsertRecords
(
    @EmployeeID int,
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @DepartmentID int,
    @Scores int
)     
As
If Exists(Select 1 From Department Where DeptID=@DepartmentID)
    Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) values(@EmployeeID,@FirstName,@LastName,@DepartmentID,@Scores)
GO--存儲過程調用begin tran Exec InsertRecords @EmployeeID=15,@FirstName='first15',@LastName='last15',@DepartmentID=2,@Scores=80 
If @@ROWCOUNT =0 Goto ErrFlagExec InsertRecords @EmployeeID=16,@FirstName='first16',@LastName='last16',@DepartmentID=4,@Scores=92 
If @@ROWCOUNT =0 Goto ErrFlagExec InsertRecords @EmployeeID=17,@FirstName='first17',@LastName='last17',@DepartmentID=5,@Scores=75 
If @@ROWCOUNT =0 Goto ErrFlagCommit Tran
Print N'Commit Tran'
Goto ExitFlagErrFlag:
Rollback Tran
Print N'Rollback Tran'ExitFlag:Go
      

Answer 10

要是在SQL Serer 2008环境，更为方便，在存储过程，通过创建表类型参数，如：
use tempdb
GO
Set nocount On 
if object_id('Department') Is Not Null Drop Table Department
if object_id('Employee') Is Not Null Drop Table Employee
    
Create Table Department(DeptID int primary key,DeptName nvarchar(50))
Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)
goInsert into Department(DeptID,DeptName) values(1,'Office'),(2,'HR'),(3,'IT'),(4,'Sales'),(5,'Other')
go

go    
--存储过程
if object_id('InsertRecords') Is Not Null
    Drop proc InsertRecords
Go
--创建表类型
if Exists(Select 1 From sys.types Where name='Employee')
Drop type Employee
Create type Employee  As Table(
  EmployeeID int,
  FirstName nvarchar(50),
  LastName nvarchar(50),
  DepartmentID int,
  Scores int
)
Go
--存储过程
Create proc InsertRecords
(
@Employee Employee Readonly
)     
As
If Exists(Select 1 From @Employee As a Where Not Exists(Select 1 From Department Where DeptID=a.DepartmentID))
begin 
Raiserror 50001 N'发现无效的DepartmentID'
End 
Else 
    Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 
Select EmployeeID,FirstName,LastName,DepartmentID,Scores From @Employee
go--存储过程调用
Declare @Employee As Employee 
Insert into @Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 
values (15,N'first15',N'last15',2,80), 
(16,N'first16',N'last16',4,92),
(17,N'first17',N'last17',5,75)Exec dbo.InsertRecords @Employee = @EmployeeGo
Select * From Employee

Answer 11

要是说那三条记录一定要写在存储过程里面使用事务控制，那么那存储过程就没有多大的意义了。倘若非要那样不可，可以参考：
use tempdb
GO
Set nocount On 
if object_id('Department') Is Not Null Drop Table Department
if object_id('Employee') Is Not Null Drop Table Employee
    
Create Table Department(DeptID int primary key,DeptName nvarchar(50))
Create Table Employee(EmployeeID  int primary key ,FirstName  nvarchar(50),LastName   nvarchar(50),DepartmentID int,Scores int)
go
Insert into Department(DeptID,DeptName) values(1,'Office'),(2,'HR'),(3,'IT'),(4,'Sales'),(5,'Other')
go  
--存储过程
if object_id('InsertRecords') Is Not Null
    Drop proc InsertRecords
Go
--存储过程
Create proc InsertRecords    
As
Begin Try
begin tran
If  not Exists(Select 1 From Department Where DeptID In(2,4,5) having Count(1)=3) 
raiserror 50001 '存在无效的DepartmentID'
Insert Into Employee(EmployeeID,FirstName,LastName,DepartmentID,Scores) 
values (15,N'first15',N'last15',2,80), 
(16,N'first16',N'last16',4,92),
(17,N'first17',N'last17',5,75)
Commit tran
End Try
Begin Catch
Declare @ErrorMessage nvarchar(2047)
Set @ErrorMessage=Error_message()
Raiserror 50001 @ErrorMessage
rollback tran
End Catch 
Go
--存储过程调用
Exec InsertRecords
Go
Select * From Employee解决问题的方法是多样的，结果是一样的。