写存储过程

最近接触的存储过程，跟老大学了点存储过程的调试、在存储过程里写try，catch。

写的存储过程。

USE [SitMesDbExt]
GO
/****** Object:  StoredProcedure [dbo].[CP_PM_CreatePackBarcode]    Script Date: 2018/1/13 18:33:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author:      <田成荣>
-- Create date: <2018-1-10>
-- Description: <生成箱号的条码>
-- ==================================================
ALTER PROCEDURE [dbo].[CP_PM_CreatePackBarcode]
    @OrderID nvarchar(64),
    @Quantity int,
    @UserID nvarchar(64),    
    @ReturnMessage nvarchar(1000) OUTPUT
AS
BEGIN

declare @SaleID nvarchar (64),@SaleIDSub nvarchar (64)   --销售订单
declare @SaleSeq nvarchar (64),@SaleSeqSub nvarchar (64) --行号
declare @BatchID int --批次号
declare @Sequence int --顺序号
DECLARE @Count INT
declare @PackNum int  --箱号数
declare @Mod int  --余数
DECLARE @PackQty INT  --装箱数
DECLARE @HutID nvarchar (64) --箱号
DECLARE @POMOrderID nvarchar (64)
declare @orderBatch nvarchar (64)
BEGIN TRY

SET @ReturnMessage='OK';

BEGIN TRAN

SET @Count =0;
SELECT  @PackQty=PackQty, @SaleID=SalesOrderID, @SaleSeq=SalesOrderSeq FROM POM_ORDER_EXT 
WHERE PomOrderID= @OrderID and DepartID='整表车间'

if isnull(@PackQty,0) = 0 
    set @ReturnMessage ='装箱数量不正确'
if isnull(@SaleSeq,'') = ''
    set @ReturnMessage ='没有销售订单序号'

if isnull(@SaleID,'') = ''
    set @ReturnMessage ='没有销售订单'
else 
BEGIN
    --@SaleID取后10，@SaleSeq取后3位。
    SET @SaleIDSub=substring(@SaleID,LEN(@SaleID)-9,LEN(@SaleID));
    SET @SaleSeqSub=substring(@SaleSeq,LEN(@SaleSeq)-2,LEN(@SaleSeq));

    --取批次号
    select @orderBatch = BatchID from SitMesDb.dbo.MMHuts where PomOrderID = @OrderID
    if @orderBatch is null
    begin
                                //COLLATE Chinese_PRC_CI_AS 不写会报错！
        SELECT  @BatchID= max(BatchID COLLATE Chinese_PRC_CI_AS)  FROM SitMesDb.dbo.MMHuts  WHERE PomOrderID in
        (select distinct convert(varchar(64),PomOrderID COLLATE Chinese_PRC_CI_AS) from POM_ORDER_EXT 
        where SalesOrderID COLLATE Chinese_PRC_CI_AS =@SaleID )

        IF @BatchID is NULL --没有原批次号默认01，有则累加
            SET @BatchID=01; 
        else
        begin
            set @BatchID = Convert (int,@BatchID);
            set @BatchID = @BatchID + 1;
        end
    end
    else
        set @BatchID = @orderBatch; 

    --取顺序号
    SELECT  @HutID= max(HutID COLLATE Chinese_PRC_CI_AS) FROM SitMesDb.dbo.MMHuts WHERE PomOrderID COLLATE Chinese_PRC_CI_AS in
        (select distinct PomOrderID from POM_ORDER_EXT where SalesOrderID =@SaleID)   

    if @HutID is null
        begin
            set @Sequence = 00001;
        end
    else
        begin
                           //截取字符串儿。
            set @Sequence = SUBSTRING(@HutID,len(@HutID)-5,len(@HutID));
            set @Sequence = Convert(Int,@Sequence);
            set @Sequence = ((@Sequence/10)*10)+11;
        end
        //求余数。
    set @Mod = @Quantity%@PackQty
    if @Mod<>0
        set @PackNum = @Quantity/@PackQty + 1;
    else
        set @PackNum = @Quantity/@PackQty; 
    --print @Count
    --print @PackNum
    --print @SaleIDSub
    --print @SaleSeqSub
    WHILE(@Count<@PackNum)
    BEGIN   
        //RIGHT('00000000'+CAST( @BatchID  AS nvarchar(50)),2),给@BatchID前边不零，最多补8个0。
        SET @HutID=@SaleIDSub+ @SaleSeqSub+ RIGHT('00000000'+CAST( @BatchID  AS nvarchar(50)),2)+ RIGHT('00000000'+CAST( @Sequence  AS nvarchar(50)),8);

        INSERT INTO SitMesDb.dbo.MMHuts(HutID,BatchID,POMOrderID,HutName,HutStatusPK,HutTypePK,LocPK,UseType,LastUser,IsReserved,IsLocked,CreatedBy,CreatedOn) 
        VALUES (@HutID,RIGHT('00000000'+CAST( @BatchID  AS nvarchar(50)),2),@OrderID,'4','1','3','1','1','1','0','0','1',GETDATE())

        print @HutID;
        SET @Sequence=Convert(int,@Sequence)+1
        SET @Count=@Count+1;
    END 

end
select HutID from SitMesDb.dbo.MMHuts where POMOrderID = @OrderID 

COMMIT TRAN 
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    set @ReturnMessage = 'NG ' -- + ERROR_MESSAGE()
    RaisError(@ReturnMessage,16,1) --抛出异常
END CATCH

END

动手+联想+反馈=成长