================以下是存储过程================/*加入一条基本信息库存商品
-1 inttostr 失败-2 有相关数据存在 -3 parid 不存在-4 记录已存在*/
CREATE PROCEDURE F_B_InsertP(@Parid varchar(25),@dbname varchar(30),@FullName varchar(66),@Namevarchar(30),@UserCode varchar(26),@Standard varchar(120),@Type varchar(40),@Area varchar(30),@Unit1 varchar(8),@Unit2 varchar(8),@UnitRate1 numeric(18,4),@UnitRate2 numeric(18,4),@preprice1 numeric(18,4),@preprice2 numeric(18,4),@LifeMonth int,@LifeDay int,@Comment varchar(256),@namepyvarchar(60),@Barcodevarchar(30))ASDeclare @nReturntype intDeclare @EtypeId_1 varchar(25)Declare @nSoncount intDeclare @nSonnum intDeclare @leveal smallintDeclare @ParRec intDeclare @checkValue int
-- 取得ID号
exec @nReturntype=F_B_createId @ParId,@dbname,@EtypeId_1 out,@nSonnum out,@nSoncount out,
@ParRec out
if @nReturntype=-101 goto error111if @nReturntype=-102 goto error112if @nReturntype=-103 goto error113
Exec @checkValue=mzw_CheckBasicSet if @checkValue=1 -- 查找编号和全名不能完全相同beginif exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname and [usercode]=@usercode) and [deleted]<>1) goto error114endif @checkValue=2--全名不能完全相同beginif exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname ) and [deleted]<>1) goto error114endif @checkValue=3--编号不能完全相同beginif exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([usercode]=@usercode) and [deleted]<>1) goto error114endif @checkValue=4 -- 查找编号和全名不能完全相同beginif exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname or [usercode]=@usercode) and [deleted]<>1) goto error114endif (len(@Barcode)>0)beginif exists(Select * From ptypeWhere ([Barcode]=@Barcode) and [deleted]<>1) return -5end
BEGIN TRAN InsertP
Select @leveal=[leveal] From ptype Where [typeid]=@ParidSelect @leveal=@leveal+1
Insert into [ptype] ([typeId], [Parid], [leveal], [soncount], [sonnum],[FullName], [Name], [UserCode], [Standard], [Type],[Area], [Unit1], [Unit2], [UnitRate1], [UnitRate2],[preprice1], [preprice2], [UsefulLifeMonth],[UsefulLifeDay], [Comment], [namepy], [parrec],[Barcode])
values (@EtypeId_1, @Parid, @leveal, 0, 0,@FullName, @Name, @UserCode, @Standard, @Type,@Area, @Unit1, @Unit2, @UnitRate1, @UnitRate2,@preprice1, @preprice2, @LifeMonth,@LifeDay, @Comment, @namepy, @parrec,@Barcode)
if @@rowcount=0 beginROLLBACK TRAN InsertP Return -1end else beginUpdate [ptype]set [sonnum]=@nSonnum+1,[soncount]=@nSoncount+1Where [typeid]=@Paridend
COMMIT TRAN InsertP
goto succee
succee:Return 0
error111:Return -1
error112:Return -2
error113:Return -3
error114:Return -4GO
///////////////////利用游标
IF len(@Parid)=25 RETURN -1DECLARE @execsql [VARCHAR](500)DECLARE @szTypeId VARCHAR(25),@sonnum INT,@par VARCHAR(25),@soncount INTSELECT @execsql=' SELECT typeid,sonnum,parid,soncount FROM '+@dbname+' WHERE typeid= '+''''+@parid+''''
DECLARE checkid_CURSOR CURSOR FOR EXEC (@execsql)OPEN checkid_CURSORFETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncountWHILE (@@FETCH_STATUS = 0)BeginSet @nSon=@sonnumSet @nCount=@soncountDECLARE @tempId VARCHAR(5),@nReturn INTSet @soncount=@soncount+1EXEC @nReturn=inttostr @soncount,@tempId outIF @nReturn=-1 BEGINCLOSE checkid_CURSORDEALLOCATE checkid_CURSORRETURN -2END ELSEBEGINIF @sztypeid='00000' Set @createdid=@tempIdELSE Set @createdid=RTRIM(@szTypeid)+@tempIdENDFETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncountEndCLOSE checkid_CURSORDEALLOCATE checkid_CURSORRETURN 1
