考虑到基础资料都有一定的共性,比如:都必须在t_Item表中有记录,ID都来源与t_Identity,上下级关系等等,为了减轻工作量,编写了如下的存储过程.
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'CreateItem'
AND type = 'P')
DROP PROCEDURE CreateItem
GO
CREATE PROCEDURE CreateItem
@ItemClassID int,
@Number varchar(50),
@Name varchar(50),
@ISDetail bit,
@SysLinkID int=null
AS
--检查核算项目类别ID的合法性，并获取核算项目的类别
if @ItemClassID<=0 or @ItemClassID is null
begin
RAISERROR ('核算项目类别ID不能为空', 16, 1)
return
end
Declare @SQLTable varchar(50),@ClassID int
Set @SQLTable=null
Set @ClassID=null
Select @SQLTable==FItemClassID
From t_ItemClass where FItemClassID=@ItemClassID
if @ClassID is null
begin
RAISERROR ('核算项目类别不存在', 16, 1)
return
end
--检查编码的合法性,并获取上级编码ID
If Exists(Select *From t_Item where FItemClassID=@ItemClassID and FNumber=@Number)
begin
RAISERROR ('编码重复,可能该核算项目已经存在', 16, 1)
return
end
Declare @ParentID varchar(20)
Declare @LastPointPos int
Set @LastPointPos=len(@Number)
while @LastPointPos>0 and substring(@)<>'.'
Set @LastPointPos=@LastPointPos-1
if @LastPointPos>0 Print 'ParentNumber:'+Substring(@)
if @LastPointPos>0
begin
Select @ParentID=null
Select @ParentID==FLevel From t_Item
where FNumber=Substring(@)
and FItemClassID=@ItemClassID
and FDetail=0
if @ParentID is null
begin
RAISERROR ('上级核算项目不存在或者不为明细', 16, 1)
return
end
Set @ShortNumber=substring(@(@Number)-@LastPointPos)
if @ShortNumber is null or len(@ShortNumber)=0
begin
RAISERROR ('编码不合规则,最后一级编码不存在', 16, 1)
return
end
end
else
Select @ParentID===@Number
--插入数据
begin transaction
Declare @ItemID int
Select @ItemID=FNext From t_Identity where FName='t_Item'
Update t_Identity Set FNext=@ItemID+1 where FName='t_Item'
Insert Into t_Item(FItemID,FItemClassID,FNumber,FParentID,FLevel,FDetail,Fname,FFullNumber,FShortNumber,FsysLinkID)
Values(@@@@@)
if @SQLTable is not null and @ISDetail=1
begin
Declare @SQL varchar(5000)
Set @SQL='Insert into '+@SQLTable
+'(FItemID,FNumber,FName,FShortNumber,FParentID) '
+'Select FItemID,FNumber,FName,FShortNumber,FParentID From t_Item where FitemClassID='
+str(@ItemClassID)
+' and FItemID='+str(@ItemID)
print @SQL
exec (@SQL)
end
commit
GO
--exec CreateItem 1,'0001.01.03','abcdef',1
这个存储过程完全把新增核算项目过程封装了,比如新增一个物料直接调用这个方法就可以建立这个物料资料,然后要添加辅助属性直接去更新t_ICItem这个表(视图)就OK了
