日期:2014-05-18  浏览次数:20630 次

寻求性能高手
SQL code
ALTER PROCEDURE [dbo].[up_PP_AddCuringInfo]
@TyreNO CHAR(10),--胎号
@GreenTyreNo CHAR(10),--成型工号
@MaterialID CHAR(9),--物料编号
@LeftOrRight CHAR(1),--左右模标志
@BeginTime VARCHAR(20),--硫化开始时间
@MachineID VARCHAR(7),--机台编号
@IDinFac varchar(20),--机台名称
@DayShiftName VARCHAR(4),--班次
@MouldID VARCHAR(10),--模具编号
@WorkerID VARCHAR(20),--操作工人名
@ShiftNo VARCHAR(16),--接班编号
@GroupName VARCHAR(10)--班组
AS
SET NOCOUNT ON
DECLARE @GroupID VARCHAR(2)--班组编号
DECLARE @TempTyreNO varchar(10)--胎号临时变量
DECLARE @TempGreenTyreNo varchar(15)--成型工号临时变量
DECLARE @SizeID varchar(10)--规格
DECLARE @PlyRatingID varchar(10)--层级
DECLARE @PatternID varchar(10)--花纹
DECLARE @BrandID varchar(10)--品牌
DECLARE @StandardID varchar(10)--标准
DECLARE @AttributeID varchar(10)--属性
DECLARE @SizeName varchar(30)--规格
DECLARE @PlyRatingName varchar(30)--层级
DECLARE @PatternName varchar(30)--花纹
DECLARE @BrandName varchar(30)--品牌
DECLARE @StandardName varchar(30)--标准
DECLARE @AttributeName varchar(30)--属性
DECLARE @MATERIALCODE varchar(30)--物料编码
DECLARE @PLANID VARCHAR(16)--计划编号
DECLARE @PLANDETAILID VARCHAR(2)--计划明细编号
DECLARE @DayShiftID VARCHAR(2)
DECLARE @GreenTyreMaterialID VARCHAR(20)--胎胚物料编号
DECLARE @GreenTyreMaterialCode VARCHAR(80)--胎胚物料名称

SET @DayShiftID=CASE @DayShiftName 
                    WHEN '早' THEN '01' 
                    WHEN '中' THEN '02' 
                    WHEN '夜' THEN '03' 
                END

--得到班组编号
SELECT @GroupID=GroupID
FROM TB_HR_Group
WHERE GroupName=@GroupName
--得到品牌、规格、层级、花纹、标准、属性
SELECT @SizeID=T1.SizeID, 
       @SizeName=T1.SizeName,
       @PlyRatingID=T1.PlyRatingID, 
       @PlyRatingName=T1.PlyRatingName,
       @PatternID=T1.PatternID, 
       @PatternName=T1.PatternName,
       @BrandID=T1.BrandID, 
       @BrandName=T1.BrandName,
       @StandardID=T1.StandardID, 
       @StandardName=T1.StandardName,
       @AttributeID=T1.AttributeID, 
       @AttributeName=T1.AttributeName,
       @MATERIALCODE=T1.MATERIALCODE
FROM V_MaterialCode T1
WHERE MaterialID=@MaterialID
--获得计划编号
SELECT @PLANID=SUBSTRING(@ShiftNo,8,6)+SUBSTRING(@ShiftNo,1,7)+SUBSTRING(@ShiftNo,14,2)+'0'


DECLARE @planCount int
SELECT @planCount=COUNT(1) 
FROM TB_PP_PLAN 
WHERE PlanID=@PLANID
IF(@planCount<=0)--如果计划表里没有该计划,则创建
BEGIN
    INSERT tb_PP_Plan(PlanID,PlanDate,PlanShift,MachineID,PlanState,RecorderID,RecordTime)
    VALUES(@PLANID,GETDATE(),@DayShiftID,@MachineID,'2',@WorkerID,GETDATE())
END

SELECT TOP 1 @PLANDETAILID=ISNULL(PLANDETAILID,'') 
    FROM TB_PP_PLANDETAIL 
    WHERE PLANID=@PLANID 
      AND MATERIALID=@MaterialID 
      AND PLANSTATE='2'

IF(@PLANDETAILID='')
BEGIN
    SELECT @PLANDETAILID='0'+ISNULL(CAST(CAST(MAX(PlanDetailID) AS INT)+1 AS VARCHAR),'1') 
    FROM tb_PP_PlanDetail WHERE planid=@PLANID
    INSERT TB_PP_PLANDETAIL(PlanID,PlanDetailID,MaterialID,PlanAmount,PlanState,PlanFlag,Remark,RecorderID,RecordTime,GroupID,RealAmount)
    VALUES(@PLANID,@PLANDETAILID,@MaterialID,200,'2','2','系统自动添加',@WorkerID,GETDATE(),@GroupID,0)
END
--获得胎胚物料
SELECT @GreenTyreMaterialID=T1.MaterialID,@GreenTyreMaterialCode=T2.MaterialCode 
FROM tb_PP_MouldingOutput T1 LEFT JOIN 
     tb_TE_Material T2 ON T1.MaterialID=T2.MaterialID
WHERE GreenTyreNo = @TyreNo

SET XACT_ABORT ON
BEGIN TRAN T1
    --添加胎胚出库信息
    UPDATE tb_PP_MouldingOutput SET OutFlag='1' WHERE GreenTyreNo=@TyreNo
    --更新胎胚库存表
    UPDATE tb_PP_MouldReMainInfo SET Amount=Amount-1 WHERE MaterialID=@GreenTyreMaterialID
    UPDATE tb_SY_tyrestate
    SET spec=@SizeID, 
        SpecName=@SizeName,
        layer=@PlyRatingID, 
        layerName=@PlyRatingName,
        texture=@PatternID, 
        textureName=@PatternName,
        Brand=@BrandID, 
        brandName=@BrandName,
        [standard]=@StandardID, 
        standardname=@StandardName,
        attribute=@AttributeID, 
        attributename=@AttributeName,
        MaterialID=@MaterialID, 
        MaterialName