SQL 存储过程

例 一：

USE [SFC]
GO
/****** Object:  StoredProcedure [dbo].[ShenBo_test]    Script Date: 2019/03/15 16:54:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ShenBo_test]
@y_isn varchar(30),--传入ISN码
@y_result int,--传入windows测试结果
@y_reMessage varchar(1000) output,--返回提示消息
@y_flag int output--返回提示参数，正确为0，错误为1
as
declare @route_step_no int
begin    
    select top 1 @route_step_no=route_step from ISN_STATUS_HISTORY where isn=@y_isn order by savedate desc
    if(@route_step_no=106)
        begin
        if(@y_result=0)
            begin
            update isn_status set route_step=116,route_step_indexof=10,passstation='105,1;106,2;120,8;' where isn=@y_isn
            insert into isn_status_history (woid,isn,shiftid,route_step,route_step_indexof,status,operater,savedate,lineid,isrepassstation,isfristbad,flag)
            select woid,isn,shiftid,route_step,route_step_indexof,status,operater,savedate,lineid,isrepassstation,isfristbad,flag from isn_status_history where isn=@y_isn and route_step=106
            update isn_status_history set route_step=120,route_step_indexof=8 where isn=@y_isn and savedate in(
            select top 1 savedate from isn_status_history where isn=@y_isn order by savedate desc
            )
            set @y_reMessage=dbo.DoFormat('PASS。',29)
            set @y_flag=0
            return
            end
        else
            begin
                set @y_reMessage=dbo.DoFormat('请返回windows测试。',29)
                set @y_flag=1
                return
            end
        end
    else
        begin
            set @y_reMessage=dbo.DoFormat('请返回炉后目检。',29)
            set @y_flag=1
            return
        end
end

 

例二：

USE SFC
GO
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME='MS_Graogucs_Card_Dos')
drop proc MS_Graogucs_Card_Dos
GO

CREATE PROC MS_Graogucs_Card_Dos
    @Isn nvarchar(50),
    @rs int output
AS
    DECLARE @y_WoId int
    DECLARE @y_ShiftId int
    BEGIN
        SELECT @rs=count(*) from ISN_Status_History where Isn=@Isn and Route_Step=121 --查询是否DOS前段开机测试上传
        if @rs<=0 or @@error>1
        begin
            set @rs=1--当返回值为1时表示DOS前工序漏测试
            return @rs
        end
        SELECT @y_ShiftId=ShiftId from ISN_Status_History where Isn=@Isn and Route_Step=106 --获取ShiftId
        SELECT @y_WoId=WoId from ISN_Status_History where Isn=@Isn and Route_Step=121 --获取订单
        SELECT @rs=count(*) from ISN_Status_History where Isn=@Isn and Route_Step=119 --查询是否已完成DOS测试
        if @rs>=1
        begin
            set @rs=0
            return @rs
        end
        update isn_status set route_step=116,route_step_indexof=10,passstation='105,1;106,2;121,4;119,6;' where Isn=@Isn and WoId=@y_WoId--更新过站信息
        insert into isn_status_history values(@y_WoId,@Isn,@y_ShiftId,119,6,0,1985,getdate(),99,0,NULL,0,'N')
        --(woid,isn,shiftid,route_step,route_step_indexof,status,operater,savedate,lineid,isrepassstation,isfristbad,flag)
        if @@error<=0
        BEGIN
            set @rs=0
            return @rs
        END
        ELSE
        BEGIN
            set @rs=2
            return @rs
        END
    END
    RETURN @rs
GO