调用存储示例

1.站点数据采集条码过站调用 检查投入条码校验 存储示例

create proc checksn 
    @lotId nvarchar(50),
    @stationId nvarchar(50),
    @routeStepId nvarchar(50),
    @userId nvarchar(50),
    @serialNumber nvarchar(50)
as 
begin
    declare @msg varchar(100)
    declare @error varchar(100)
 if(right(@serialNumber,4)>'2100' or right(@serialNumber,4)<'2001')
  begin
     set @msg='验证失败 ,后缀不在2001 - 2100 范围内！'
   set @error=1 
  end
 else
  begin
     set @msg='验证通过！'  
   set @error=0 
    end  

 select @error AS errorcode ,@msg AS msg
end

2.装配检验存储过程示例

ALTER PROCEDURE [dbo].[SPD_SZCheckBind]  
    @productId nvarchar(100),  
    @lotId nvarchar(100),  
    @stationId nvarchar(100),  
    @routeStepId nvarchar(100),  
    @userId nvarchar(100),  
    @masterBoxNo nvarchar(100),   --装配条码
    @boxNo nvarchar(100)    -- 主条码
AS  
BEGIN  
    -- 定义变量来存储去除逗号及其后面字符的masterBoxNo  
    DECLARE @adjustedMasterBoxNo nvarchar(100)  

    -- 找到逗号的位置  
    DECLARE @commaIndex INT = CHARINDEX(',', @masterBoxNo)  

    -- 如果找到了逗号，则去除逗号及其后面的所有字符  
    IF @commaIndex > 0  
    BEGIN  
        SET @adjustedMasterBoxNo = LEFT(@masterBoxNo, @commaIndex - 1)  
    END  
    ELSE  
    BEGIN  
        -- 如果没有找到逗号，则直接使用原字符串  
        SET @adjustedMasterBoxNo = @masterBoxNo  
    END  

    -- 验证@adjustedMasterBoxNo和@boxNo是否一致  
    IF @adjustedMasterBoxNo = @boxNo  
    BEGIN  
        -- 验证通过  
        SELECT 0 AS errorcode, '验证通过' AS msg  
    END  
    ELSE  
    BEGIN  
        -- 验证失败  
        SELECT 1 AS errorcode, '条码验证失败：过站sn去除逗号后部分与装配内容不一致' AS msg  
    END  
END

3.装箱条码验证存储示例

ALTER PROCEDURE [dbo].[CHECK_PSN_IS_ORDER_SCAN]  
    @productId nvarchar(100),
    @lotId nvarchar(100),  
        @stationId nvarchar(100),
        @routeStepId nvarchar(100),
        @userId nvarchar(100),
        @boxId nvarchar(100),
        @serialNumberID nvarchar(100),
    @serialNumber VARCHAR(100)   
AS  
BEGIN  

    --insert into [Udit.sys].[dbo].[sys_public]([key],[val]) values('CHECK_PSN_IS_ORDER_SCAN',@lotId+@serialNumber)
    declare @last_psn varchar(100)
        declare @first_psn varchar(100)
        DECLARE @PSN varchar(100) 
    declare @lot_name varchar(100) 
        declare @msg varchar(100)
        declare @error varchar(100)
    -- 找到该工单最后一个装箱的条码的客户码
        -- 找到工单
        select @lot_name=lot_name from qs_lots(nolock) where lot_id=@lotId 
        -- 找到客户水排码
        select @PSN=ASSEMBLY_SN from QS_ASSEMBLY(nolock) where serial_Number=@serialNumber
        --set @PSN=@serialNumber
        -- 找到本工单未装箱的第一个水排码

        select top 1 @first_psn=customer_sn from [udit.csr].dbo.AP_CUSTOMER_SN(NOLOCK) where po=@lot_name order by customer_sn asc  

    select top 1 @last_psn=ASSEMBLY_SN  from QS_ASSEMBLY(nolock) bing   
            left join CT_BOX_ITEMS(nolock) item  on item.SERIAL_NUMBER=bing.serial_number
            where bing.lot_id=@lotId 
            and item.serial_number is null
            order by ASSEMBLY_SN asc

        if (@last_psn is null) 

            begin
             if(@PSN=@first_psn)
          begin 
                    set @msg='验证通过！'  
                        set @error=0 
                        --SELECT 0 AS errorcode, '验证通过！' AS msg
          end
             else 
          begin 
                        set @msg='请从第一个开始扫描'
                        set @error=1
                         --SELECT 1 AS errorcode, '请从第一个开始扫描！' AS msg  
          end

                select @error AS errorcode ,@msg AS msg
            end
        else 
          begin 
            -- 找到下一个水排码，如果和扫描的不一致，则提示
             if(@PSN=@last_psn)
                        begin 
                         set @msg='验证通过！' 
                         set @error=0
                         --SELECT 0 AS errorcode, '验证通过！' AS msg
                        end 
                 else

                     begin 
                     set @msg='未按客户水排码顺序扫描，请更换！应扫描 :'+convert(varchar(100),@last_psn)
                        set @error=1
                        --SELECT 1 AS errorcode, '未按客户水排码顺序扫描，请更换！应扫描 :'+convert(varchar(100),(@last_psn+1)) AS msg 
                     end 

                select @error AS errorcode ,@msg AS msg
            end        
END