关于存储过程的一部分不太理解

dahaizhiliang 2013-01-15 02:42:08

CREATE Procedure P_RM_RepairSparepartsBillDetail_Add
@OperatorPointCode VARCHAR(32) = NULL, -- 操作者所在网点号
@PointCode varchar(32)=null,
@YesNOFreeze int =null,
@RepairBillCode varchar(32)=null,
@SerialNo int=null ,
@RepairJobSerialNo varchar(32)=null,
@SparepartID varchar(32)=null,
@SparepartCode varchar(32)=null,
@SparepartName varchar(128)=null,
@PlanCompleteTime DATETIME =NULL,
@StorageCode varchar(32)=null,
@Unit varchar(32)=null,
@SalePrice decimal(12,2)=null,
@RequireAmount decimal(12,2)=null,
@FreezeAmount decimal(12,2)=null,
@RepairType VARCHAR(32)=null,
@Discount decimal(12,2)=null,
@FeeType varchar(32)=NULL,
@OperatorCode varchar(32),
@Factoryprice decimal(12,2)=null,
@RepairJobType varchar(32),
@AddFlag int =0

AS
BEGIN
DECLARE @StoreNum decimal(12,2) --库存数量
DECLARE @LackAmount decimal(12,2) --记录当前冻结数量
DECLARE @FactoryPay INT --厂家付费标志
DECLARE @FeeFlag INT --记录是否客户付费标记
IF @PointCode IS NULL OR @PointCode=''
SET @PointCode=@OperatorPointCode
--查询厂家付费、客户付费标志,如本网点没有此费用类别,则用上级网点数据
;WITH Args AS
(
SELECT 1 AS OrderID , b.pointcode,b.parentpointcode
FROM T_PointInfo B WITH(NOLOCK)
WHERE B.PointCode = @PointCode

UNION ALL
SELECT b.OrderID + 1 AS OrderID , a.pointcode,a.parentpointcode
FROM T_PointInfo A WITH(NOLOCK) ,Args B
WHERE a.PointCode = B.parentpointcode
)
SELECT TOP 1 @FactoryPay = ISNULL(A.factoryflag,0),@FeeFlag = ISNULL(A.FeeFlag , 1)
FROM T_SparepartsFeeType A WITH(NOLOCK), Args B WITH(NOLOCK)
WHERE a.PointCode = B.PointCode AND A.feetype =@FeeType
ORDER BY b.Orderid desc

IF @SerialNo IS NULL --新增模式
BEGIN
SELECT @SerialNo = ISNULL(MAX(SerialNo),0)+1 FROM T_RepairSparepartsBill
END
IF @YesNOFreeze = 1
BEGIN
--更新备件库存表
UPDATE T_SparepartsInventory
SET
FreezeAmount = @FreezeAmount + FreezeAmount
WHERE PointCode = @PointCode AND SparepartsID = @SparepartID
-- 若异常不为零返回-1，更新失败
IF @@ERROR <> 0
RETURN 70110501
END
ELSE
BEGIN
SET @FreezeAmount = 0
END
INSERT INTO T_RepairSparepartsBill
(PointCode,RepairBillCode,RepairJobSerialNo,SerialNo,SparepartID,SparepartCode,SparepartName,StorageCode,
Unit,SalePrice,RequireAmount,Discount,FeeType,FeeFlag,DataSource,OutAmount,FreezeAmount,RepairType,RepairJobType,FactoryPay,Factoryprice,AddFlag)
VALUES(@PointCode,@RepairBillCode,@RepairJobSerialNo,@SerialNo,@SparepartID,@SparepartCode,@SparepartName,@StorageCode,
@Unit,@SalePrice,@RequireAmount,@Discount,@FeeType,@FeeFlag,'维修备件',0,@FreezeAmount,@RepairType,@RepairJobType,@FactoryPay,@Factoryprice,@AddFlag)
-- 若异常不为零返回-1，插入失败
IF @@ERROR <> 0
RETURN 70110502

END

...全文