存储过程使用技巧_数据库技巧

================以下是存储过程================
/*
加入一条基本信息
库存商品

-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)
)
AS
Declare @nReturntype int
Declare @EtypeId_1 varchar(25)
Declare @nSoncount int
Declare @nSonnum int
Declare @leveal smallint
Declare @ParRec int
Declare @checkValue int

— 取得ID号

exec @nReturntype=F_B_createId @ParId,@dbname,@EtypeId_1 out,@nSonnum out,@nSoncount out,

@ParRec out

if @nReturntype=-101 goto error111
if @nReturntype=-102 goto error112
if @nReturntype=-103 goto error113

Exec @checkValue=mzw_CheckBasicSet
if @checkValue=1 — 查找编号和全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
and [usercode]=@usercode) and [deleted]<>1) goto error114
end
if @checkValue=2–全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
) and [deleted]<>1) goto error114
end
if @checkValue=3–编号不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or (
[usercode]=@usercode) and [deleted]<>1) goto error114
end
if @checkValue=4 — 查找编号和全名不能完全相同
begin
if exists(Select [typeid] From ptype Where [typeId]=@EtypeId_1 or ([fullname]=@fullname
or [usercode]=@usercode) and [deleted]<>1) goto error114
end
if (len(@Barcode)>0)
begin
if exists(Select * From ptype
Where ([Barcode]=@Barcode) and [deleted]<>1) return -5
end

BEGIN TRAN InsertP

Select @leveal=[leveal] From ptype Where [typeid]=@Parid
Select @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
begin
ROLLBACK TRAN InsertP
Return -1
end
else
begin
Update [ptype]
set [sonnum]=@nSonnum+1,[soncount]=@nSoncount+1
Where [typeid]=@Parid
end

COMMIT TRAN InsertP

goto succee

succee:
Return 0

error111:
Return -1

error112:
Return -2

error113:
Return -3

error114:
Return -4
GO

///////////////////利用游标

IF len(@Parid)=25 RETURN -1
DECLARE @execsql [VARCHAR](500)
DECLARE @szTypeId VARCHAR(25),@sonnum INT,@par VARCHAR(25),@soncount INT
SELECT @execsql= SELECT typeid,sonnum,parid,soncount FROM
+@dbname+ WHERE typeid= ++@parid+

DECLARE checkid_CURSOR CURSOR FOR
EXEC (@execsql)
OPEN checkid_CURSOR
FETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncount
WHILE (@@FETCH_STATUS = 0)
Begin
Set @nSon=@sonnum
Set @nCount=@soncount
DECLARE @tempId VARCHAR(5),@nReturn INT
Set @soncount=@soncount+1
EXEC @nReturn=inttostr @soncount,@tempId out
IF @nReturn=-1
BEGIN
CLOSE checkid_CURSOR
DEALLOCATE checkid_CURSOR
RETURN -2
END
ELSE
BEGIN
IF @sztypeid=00000
Set @createdid=@tempId
ELSE
Set @createdid=RTRIM(@szTypeid)+@tempId
END
FETCH NEXT FROM checkid_CURSOR INTO @szTypeId, @sonnum, @par,@soncount
End
CLOSE checkid_CURSOR
DEALLOCATE checkid_CURSOR
RETURN 1