SQL Server存储过程出错
用的存储过程是
alter PROCEDURE [dbo].[ImprotToEmployee]
@username varchar(50),
@deptname varchar(50),
@teamname varchar(50),
@cardno bigint,
@corpid int
AS
declare @deptId int
declare @teamId int
set @deptId=-1
set @teamId=-1
set @deptId=(SELECT top 1 [deptId] FROM [ABDoor].[dbo].[Department]
where [orgId]=@corpid and [deptName]=@deptname and [isValid]='true')
set @deptname = (SELECT top 1 [deptName] FROM [ABDoor].[dbo].[Department] where deptId = @deptId )
--print @deptId
if @deptId=-1
begin
insert into Department (orgId, deptName, isValid) values (@corpid,@deptname,1 )
SELECT @deptId = @@IDENTITY
end
set @teamId=(select officeId from Office where deptId = @deptId and officeName =@teamname and isValid = 'True')
if @teamId=-1
begin
insert into Office (deptId, officeName, isValid) values(@deptId,@teamname,1 )
SELECT @teamId = @@IDENTITY
end
insert into Employee([name], officeId, cardNumber) values (@username,@teamId,@cardno)
不过调用时 -- [ImprotToEmployee] 'abc','测试部门','123',1,197
提示消息 515,级别 16,状态 2,过程 ImprotToEmployee,第 33 行
不能将值 NULL 插入列 'officeId',表 'ABDoor.dbo.Employee';列不允许有空值。INSERT 失败。
语句已终止。
我因为给的值@deptname,@teamname有值,而@deptId,@teamId没有值
------解决方案--------------------
如果你业务上的'ABDoor.dbo.Employee'表中officeid允许为空,那么表结构那里就设为【允许为null】,你目前的表是不能为null的,如果业务不允许,那么你就付个默认值就可以了