Post reply

Cursor replacement suggestions

  • July 18, 2021 at 7:43 pm

    #3911458

    I typically use SSIS to do that, since it has the inherent capability to run multiple imports async.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • July 18, 2021 at 8:35 pm

    #3911467

    Does not matter what possible options we give you to process from the queue - unless you tells with great detail what you are doing with each row on the queue, what is the performance issue you are getting (is it locks? dead locks? plain slowness?) and a lot more detail, giving you a option to process with a Queue or with SSIS is not necessarily going to fix you issue - and in the meantime you lost time implementing a solution of which the only benefit you got is that you learn another way of doing a particular type of processing.

    if you are so keen on cursors then it is likely that your remaining code is also done in a "row by row" approach - and if this is the case then that is likely your biggest problem and that is what you should be addressing first.

     

  • July 26, 2021 at 6:02 pm

    #3914032

    Each one of the import records from the queue are processed with the cursor, as the count from the capture is associated with a TEST. I went ahead and created a sql agent job for a given oven\wireline so I could multi-process from the Import Table. Thinking this would speed up processing.

    ex.

    usp_process_line '205e01%'

    usp_process_line '205e02%'

    There are 5 different test per data record that can be pass to procedure "Capture_Inline_Quality". I have included one of those TESTs in the code snippet below.  I tried to bypass the call to procedure if the counts where zero(0) meaning no alarming needed, but found I needed those records for a reporting process where it performs counts and averages on the data received. If I excluded those records it threw off the counts. Is there a better way to process the data.

    Thanks.

    -- Declare cursor to read inserted records.
    EXECUTE Write_Error_Log 'INFORMATION','Declaring quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    DECLARE Quality_Cursor CURSOR LOCAL DYNAMIC READ_ONLY FOR
    SELECT DISTINCT [Key], [Date], [Time], Small_Beads, Large_Beads, Continuity, Speed, Continuity_Voltage, 
    Continuity_Amp, Small_Bead_Threshold, Large_Bead_Threshold, Equip_ID,XLarge_Beads,XLarge_Bead_Threshold
    FROM Import_Smartkatt_3
    --WHERE Processed = N'FALSE'-- Revision 1 - Removed
    WHERE (Processed = N'FALSE') AND (Equip_ID LIKE @Equipment)-- Revision 1 - Added
    ORDER BY [Date],[Time]

    -- Open cursor with the records.
    EXECUTE Write_Error_Log 'INFORMATION','Opening quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    OPEN Quality_Cursor

    -- Read first record from cursor and assign values into internal variables.
    EXECUTE Write_Error_Log 'INFORMATION','Reading first record from quality cursor','Import_Smartkatt_3_Data'-- Revision 1 - Added
    FETCH NEXT FROM Quality_Cursor
    INTO @Key, @Date, @Time, @SmallBeads, @LargeBeads, @Continuity, @Speed, @ContinuityVoltage, @ContinuityAmp, 
    @SmallBeadThreshold, @LargeBeadThreshold, @EquipID,@XLargeBeads,@XLargeBeadThreshold

    -- Loop while the cursor is active.
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Mark import row as processed.
    SET @Error_Log_Desc = 'Updating import record "' + CAST(@Key AS nvarchar(MAX)) + '" as processed'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Import_Smartkatt_3
    SET Processed = @Passed, Comments = @Comments
    WHERE [Key] = @Key
    END TRY-- Revision 1 - Added

    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update import record "' + CAST(@Key AS nvarchar(MAX)) + '" as processed'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Error = 1
    END CATCH-- Revision 1 - Added (End)

    -- Check if a PLC id is provided as the equipment.
    IF (SELECT COUNT(*) FROM Equip_Attribute WHERE Attribute = N'PLC ID' AND Attribute_Value = @EquipID) > 0-- Revision 1 - Added (Begin)
    BEGIN
    -- Retrieve equipment id for specified PLC id.
    SET @Error_Log_Desc = 'Retrieving equipment id associated with PLC id "' + @EquipID + '"'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SELECT TOP 1 @EquipID = Equip_ID
    FROM Equip_Attribute
    WHERE Attribute = N'PLC ID' AND Attribute_Value = @EquipID
    END-- Revision 1 - Added (End)

    SET @Oven = ISNULL(dbo.ParseText(@EquipID,'-',1),'')
    SET @WireLine = ISNULL(dbo.ParseText(@EquipID,'-',2),'')

    -- Save continuity test data.
    IF ISNULL(@ContinuityTestID,'') = '' 
    BEGIN
    SET @Error_Log_Desc = 'No "' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + '" test id available'-- Revision 1 - Added (Begin)
    EXECUTE Write_Error_Log 'WARNING',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Comments += 'WARNING - No ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test id available' + CHAR(13) + CHAR(10)-- Revision 1 - Added (End)
    --SET @Comments += 'ERROR - No ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test id available' + CHAR(13) + CHAR(10)-- Revision 1 - Removed
    END
    BEGIN
    SET @Error_Log_Desc = 'Executing CAPTURE_INLINE_QUALITY with parameters ''' + @ContinuityTestID + ''',''' + CAST(@Date AS nvarchar(MAX)) + ''','''-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += CAST(@Time AS nvarchar(MAX)) + ''',''' + @Oven + ''',''' + @Wireline + ''',''' + CAST(@Continuity AS nvarchar(MAX)) + ''',@Result OUT'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Import_Smartkatt_3_Data'-- Revision 1 - Added (End)

    --EXECUTE Capture_Inline_Quality @ContinuityTestID,@Date,@Time,@Oven,@WireLine,@Continuity,@Result OUT-- Revision 1 - Removed
    BEGIN TRY-- Revision 1 - Added (Begin)
    EXECUTE @Error = Capture_Inline_Quality @ContinuityTestID,@Date,@Time,@Oven,@WireLine,@Continuity,@Result OUT
    END TRY

    -- Log errors.
    BEGIN CATCH
    SET @Error_Log_Desc = 'Cannot save "' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + '" test data for ' + @Oven + '-' + @WireLine
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data'
    SET @Error = ERROR_NUMBER()
    END CATCH-- Revision 1 - Added (End)

    --IF @Result = 'FAIL'-- Revision 1 - Removed
    IF @Result = 'FAIL' OR @Error <> 0-- Revision 1 - Added
    BEGIN
    SET @Error_Log_Desc = 'Cannot execute CAPTURE_INLINE_QUALITY with parameters ''' + @ContinuityTestID + ''',''' + CAST(@Date AS nvarchar(MAX)) + ''','''-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += CAST(@Time AS nvarchar(MAX)) + ''',''' + @Oven + ''',''' + @Wireline + ''',''' + CAST(@Continuity AS nvarchar(MAX)) + ''',@Result OUT'
    SET @Error = 2
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Import_Smartkatt_3_Data',@Error-- Revision 1 - Added (End)
    SET @Comments += 'ERROR - Cannot save ' + ISNULL(@ContinuityTestName,'CONTINUITY FAULTS') + ' test data for ' + @Oven + '-' + @WireLine + CHAR(13) + CHAR(10)
    END
    END

     

  • July 29, 2021 at 3:36 am

    #3914925

    Any code suggestions, and how to run multiple process against the data?

     

    THanks.

  • August 5, 2021 at 1:31 pm

    #3917153

    Any suggestions?

    Thanks I can supply more info if needed..

  • August 5, 2021 at 1:46 pm

    #3917157

    from my previous post "what is the performance issue you are getting (is it locks? dead locks? plain slowness?)"

  • August 5, 2021 at 1:49 pm

    #3917158

    Slowness...using cursor and maybe a better way to process other than -- sql agent jobs..

    Thanks.

  • August 7, 2021 at 4:36 am

    #3917654

    The whole reason for the RBAR of a cursor here is so that you can call the Capture_Inline_Quality stored procedure for each and every bloody row.  That, good sir, is your real problem.  Whatever that process is, it needs to be converted to a set-based version an used appropriately.  NOTHING else is going to be of any major significance in improving this code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 7, 2021 at 2:03 pm

    #3917774

    Not sure how to attack the set based operation from from the Import queue...

    any suggestions examples

  • August 7, 2021 at 2:19 pm

    #3917775

    you need to look at what each of the procs is doing and determine what really needs to be done and what is not really required - most of the code you have about is just adding entries to a log table instead of working out the data. (and this may be part of the requirement).

    but proc Capture_Inline_Quality  is likely where the real work is done.

    and you need to see if there are dependencies between processing one record on the queue and the next one(s) - this is business rules of which we know nothing about and without having the FULL code as well as an explanation of what is the intention of the code it is rather hard to help other than suggesting approaches.

     

  • August 9, 2021 at 3:05 pm

    #3918157

    Would it help if I post Capture_Inline_Quality ?

    Thanks.

     

  • August 9, 2021 at 3:19 pm

    #3918163

    "It Depends".  If it also calls stuff in a RBAR fashion, then not really.  Someone needs to define what it's supposed to do and then create a new stored procedure that does it in a set based manner rather than RBAR.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 11, 2021 at 12:08 am

    #3918588

    I'm receiving from multiple data sources(machines\Lines) information that must be tested based upon a TESTID assigned to a

    record type whether or not it falls between a TargetMin and TargetMax for the TEST.  The records are passed into the queue and then are picked up to be processed. There are between 4-5 tests to be performed per data record received in the queue. The results of these tests are then written to Quality tables to record the Pass\Fail results. How can I get this into a set based operation seeing is how I need multiple parts of the record to be tested

    THanks for your comments.. any suggestions to get me started...

     

  • August 11, 2021 at 1:07 am

    #3918605

    Bruin wrote:

    Would it help if I post Capture_Inline_Quality ? 

    Yeah, we can't anything to improve things without seeing that code.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • August 13, 2021 at 4:40 pm

    #3919729

    I have it heavily logged so I can debug or trace data coming from the calling process.

     

    This is called for each of the alarms\alerts in prior SP.

    Thanks for looking at the code.

    INPUTS:
    --1) Test ID (Required)
    --2) Date (Optional - will default to current date)
    --3) Time (Optional - will default to current time)
    --4) Oven (Required)
    --5) Wireline (Required)
    --6) Values (Required)


    -- Input parameters.
    @Quality_Test_ID nvarchar(MAX) = NULL, 
    @Quality_Date nvarchar(MAX) = NULL,
    @Quality_Time nvarchar(MAX) = NULL,
    @Quality_Oven nvarchar(MAX) = NULL,
    @Quality_WL nvarchar(MAX) = NULL,
    @Quality_Values nvarchar(MAX) = NULL,-- Comma separated for multiple values.

    -- Output parameters.
    @SQL_Result nvarchar(MAX)= NULL OUT

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Begin procedure.
    EXECUTE Write_Error_Log 'INFORMATION','Beginning procedure (Capture_Inline_Quality)','Capture_Inline_Quality'-- Revision 1 - Added (Begin)

    -- Set defaults.
    SET @Quality_Date = CAST(ISNULL(@Quality_Date,GETDATE()) AS date)
    SET @Quality_Time = CAST(ISNULL(@Quality_Time,GETDATE()) AS time)-- Revision 1 - Added (End)

    -- Internal variables.
    EXECUTE Write_Error_Log 'INFORMATION','Declaring internal variables','Capture_Inline_Quality'-- Revision 1 - Added
    DECLARE @Done AS int = 0
    DECLARE @Error AS int = 0
    DECLARE @Quality_ID AS int
    DECLARE @Quality_DateTime as datetime
    DECLARE @Quality_Employee_ID AS nvarchar(MAX)
    DECLARE @Quality_Equip_ID AS nvarchar(MAX)
    DECLARE @Quality_Equip_Group AS nvarchar(MAX)
    DECLARE @Quality_Container_ID AS nvarchar(MAX)
    DECLARE @Quality_WO_ID AS nvarchar(MAX)
    DECLARE @Quality_Part_No AS nvarchar(MAX)
    DECLARE @Quality_Min AS nvarchar(MAX)
    DECLARE @Quality_Tgt_Min AS nvarchar(MAX)
    DECLARE @Quality_Tgt AS nvarchar(MAX)
    DECLARE @Quality_Tgt_Max AS nvarchar(MAX)
    DECLARE @Quality_Max AS nvarchar(MAX)
    DECLARE @Quality_Reason AS nvarchar(MAX)
    DECLARE @Quality_Value AS nvarchar(MAX)
    DECLARE @Quality_Value_Count AS int = 1
    DECLARE @Quality_Value_Sum AS float = 0
    DECLARE @Quality_Sample_Size AS int = 1
    DECLARE @Quality_Pass_Size AS int = 1
    DECLARE @Quality_Pass_Fail AS nvarchar(MAX)
    DECLARE @Quality_Test_Required AS nvarchar(MAX)
    DECLARE @Quality_Result AS nvarchar(MAX)
    DECLARE @Quality_Comment AS nvarchar(MAX)
    DECLARE @Equip_Status AS nvarchar(MAX)
    DECLARE @Equip_Status_Code AS nvarchar(MAX)
    DECLARE @Error_Log_Desc AS nvarchar(MAX)-- Revision 1 - Added

    -- Check for required inputs.
    IF ISNULL(@Quality_Test_ID,'') = '' OR ISNULL(@Quality_Oven,'') = '' OR ISNULL(@Quality_WL,'') = '' OR ISNULL(@Quality_Values,'') = ''-- Revision 1 - Added (Begin)
    BEGIN
    -- Log error.
    SET @Error = 1
    EXECUTE Write_Error_Log 'ERROR','Required inputs not specified','Capture_Inline_Quality',@Error

    -- Exit procedure.
    GOTO Check_For_Errors
    END-- Revision 1 - Added (End)

    -- Begin transaction.
    EXECUTE Write_Error_Log 'INFORMATION','Beginning transaction (Capture_Inline_Quality)','Capture_Inline_Quality'-- Revision 1 - Added
    BEGIN TRANSACTION Capture_Inline_Quality

    SET @Quality_Equip_ID = @Quality_Oven + '-' + @Quality_WL

    -- Save transaction.
    EXECUTE Write_Error_Log 'INFORMATION','Saving transaction (Capture_Inline_Quality)','Capture_Inline_Quality'-- Revision 1 - Added
    SAVE TRANSACTION Capture_Inline_Quality

    -- Verify that equipment is up before continuing.
    SET @Error_Log_Desc = 'Retrieving status from equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Equip_Status = [Status] 
    FROM Equip
    WHERE Equip_ID = @Quality_Equip_ID

    IF ISNULL(@Equip_Status,'') <> 'INACTIVE'
    BEGIN
    SET @Error_Log_Desc = 'Retrieving downtime code from equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Equip_Status_Code = Attribute_Value 
    FROM Equip_Attribute
    WHERE Equip_ID = @Quality_Equip_ID AND Attribute = N'Status Code'
    END 

    IF ISNULL(@Equip_Status_Code,'') <> '' OR ISNULL(@Equip_Status,'') = 'INACTIVE' OR ISNULL(@Equip_Status,'') = ''
    BEGIN
    -- Log error.
    SET @Error = 2-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Equipment "' + @Quality_Equip_ID + '" is not currently running'
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error-- Revision 1 - Added (End)

    -- Exit procedure.
    GOTO Commit_Transaction
    END

    SET @Quality_Employee_ID = N'00000'
    SET @Quality_DateTime = CAST(CAST(@Quality_Date AS date) AS datetime) + CAST(CAST(@Quality_Time AS time) AS datetime)
    SET @Quality_Reason = N'TEST'

    -- Verify that the data is within the last 4 hours before continuing.
    IF ISNULL(@Quality_DateTime,'') = '' OR @Quality_DateTime < DATEADD(HOUR,-4,GETDATE())
    BEGIN
    -- Log error.
    SET @Error = 3-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Data received for equipment "' + @Quality_Equip_ID + '" is older than 4 hours'
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error-- Revision 1 - Added (End)

    -- Exit procedure.
    GOTO Commit_Transaction
    END

    -- Verify if equipment is in a status other than ACTIVE.
    IF ISNULL(@Equip_Status,'') <> 'ACTIVE'
    BEGIN
    -- Log warning and continue.
    SET @Error_Log_Desc = 'Equipment is currently in "' + @Equip_Status + '" status'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'WARNING',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    SET @Quality_Comment = 'In ' + @Equip_Status + ' status.'
    END

    -- Get currently running container on equipment.
    SET @Error_Log_Desc = 'Retrieving currently running container on equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Container_ID = Attribute_Value 
    FROM Equip_Attribute 
    WHERE Equip_ID = @Quality_Equip_ID AND Attribute = N'Current Spool'

    -- Verify that a container is currently running.
    IF ISNULL(@Quality_Container_ID,'') = ''
    BEGIN
    -- Log error.
    SET @Error = 4-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'A container is not currently running on equipment "' + @Quality_Equip_ID + '"'
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error-- Revision 1 - Added (End)

    -- Exit procedure.
    GOTO Commit_Transaction
    END

    -- Get equipment group.
    SET @Error_Log_Desc = 'Retrieving group from equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Equip_Group = Equip_Group 
    FROM Equip 
    WHERE Equip_ID = @Quality_Equip_ID

    -- Verify that an equipment group is available.
    IF ISNULL(@Quality_Equip_Group,'') = ''-- Revision 1 - Added (Begin)
    BEGIN
    -- Log error.
    SET @Error = 5
    SET @Error_Log_Desc = 'An equipment group is not available for equipment "' + @Quality_Equip_ID + '"'
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error

    -- Exit procedure.
    GOTO Commit_Transaction
    END-- Revision 1 - Added (End)

    -- Get currently running work order on equipment.
    SET @Error_Log_Desc = 'Retrieving currently running work order on equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_WO_ID = Attribute_Value 
    FROM Equip_Attribute 
    WHERE Equip_ID = @Quality_Equip_ID AND Attribute = N'Current WO'

    -- Verify that a work order is currently running.
    IF ISNULL(@Quality_WO_ID,'') = ''-- Revision 1 - Added (Begin)
    BEGIN
    -- Log warning and continue.
    SET @Error_Log_Desc = 'A work order is not currently running on equipment "' + @Quality_Equip_ID + '"'
    EXECUTE Write_Error_Log 'WARNING',@Error_Log_Desc,'Capture_Inline_Quality'
    END-- Revision 1 - Added (End)

    -- Get currently running part number on equipment.
    SET @Error_Log_Desc = 'Retrieving currently running part number on equipment "' + @Quality_Equip_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Part_No = Attribute_Value 
    FROM Equip_Attribute 
    WHERE Equip_ID = @Quality_Equip_ID AND Attribute = N'Current Part No.'

    -- Get part no. from work order if one is not available on equipment.
    IF ISNULL(@Quality_Part_No,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving currently running part number from work order "' + @Quality_WO_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Part_No = ISNULL(Item_ID,'') 
    FROM Workorder 
    WHERE WO_ID = @Quality_WO_ID
    END

    -- Verify that a part number is available.
    IF ISNULL(@Quality_Part_No,'') = ''-- Revision 1 - Added (Begin)
    BEGIN
    -- Log error.
    SET @Error = 6
    SET @Error_Log_Desc = 'A part number is not currently available for equipment "' + @Quality_Equip_ID + '"'
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error

    -- Exit procedure.
    GOTO Commit_Transaction
    END-- Revision 1 - Added (End)

    -- Get quality specifications from product spec using part number, equipment group and test id.
    SET @Error_Log_Desc = 'Retrieving quality specifications for part number "' + @Quality_Part_No-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += '", equipment group "'+ @Quality_Equip_Group + '", and test id "' + @Quality_Test_ID + '"'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added (End)
    SELECT @Quality_Min = NULLIF(PST.Test_Min,'MO'), @Quality_Tgt_Min = NULLIF(PST.Test_Tgt_Min,'MO'), 
    @Quality_Tgt = NULLIF(PST.Test_Tgt,'MO'), @Quality_Tgt_Max = NULLIF(PST.Test_Tgt_Max,'MO'), 
    @Quality_Max = NULLIF(PST.Test_Max,'MO'), @Quality_Sample_Size = PST.Test_Sample_Size, 
    @Quality_Pass_Size = PST.Test_Pass_Size, @Quality_Pass_Fail = PST.Test_Pass_Fail, 
    @Quality_Test_Required = ISNULL(NULLIF(PST.Test_Required,''),QTA.Attribute_Value)
    FROM Product_Spec_Testing PST LEFT OUTER JOIN Quality_Test_Attribute QTA ON 
    (PST.Test_ID = QTA.Test_ID AND QTA.Attribute = 'Required')
    WHERE PST.Part_No = @Quality_Part_No AND 
    PST.Machine_Group = @Quality_Equip_Group AND 
    PST.Test_ID = @Quality_Test_ID

    -- Get default test required quality specification when one is not available on product spec.
    IF ISNULL(@Quality_Test_Required,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default test requirement from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Test_Required = Attribute_Value
    FROM Quality_Test_Attribute
    WHERE Test_ID = @Quality_Test_ID AND Attribute = 'Required'
    END

    -- Verify that test is required before continuing.
    IF ISNULL(@Quality_Test_Required,'') = '' OR ISNULL(@Quality_Test_Required,'') = N'False' OR ISNULL(@Quality_Test_Required,'') = N'0'
    BEGIN
    -- Log information.
    SET @Error_Log_Desc = 'Test id "' + @Quality_Test_ID + '" is not required for part number "'-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += @Quality_Part_No + '" that is currently running on equipment "' + @Quality_Equip_ID + '"'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added (End)

    -- Exit procedure.
    GOTO Commit_Transaction
    END

    -- Get default pass/fail quality specification when one is not available on product spec.
    IF ISNULL(@Quality_Pass_Fail,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default PASS/FAIL specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Pass_Fail = Attribute_Value
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Pass/Fail'
    END

    -- Check if quality test is a pass/fail test.
    IF ISNULL(@Quality_Pass_Fail,'') = N'True'
    -- When quality test is a pass/fail test.
    BEGIN
    SET @Error_Log_Desc = 'Test id "' + @Quality_Test_ID + '" is a PASS/FAIL test'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Min = N'1'
    SET @Quality_Tgt_Min = NULL
    SET @Quality_Tgt = NULL
    SET @Quality_Tgt_Max = NULL
    SET @Quality_Max = NULL
    END
    ELSE
    BEGIN
    SET @Error_Log_Desc = 'Test id "' + @Quality_Test_ID + '" is NOT a PASS/FAIL test'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    -- Get default min. quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Min,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default min. quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Min = NULLIF(Attribute_Value,'MO') 
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Test Min.'
    END

    -- Get default tgt. min. quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Tgt_Min,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default tgt. min. quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Tgt_Min = NULLIF(Attribute_Value,'MO') 
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Test Tgt. Min.'
    END

    -- Get default tgt. quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Tgt,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default tgt. quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Tgt = NULLIF(Attribute_Value,'MO') 
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Test Tgt.'
    END

    -- Get default tgt. max. quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Tgt_Max,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default tgt. max. quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Tgt_Max = NULLIF(Attribute_Value,'MO') 
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Test Tgt. Max.'
    END

    -- Get default max. quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Max,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default max. quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Max = NULLIF(Attribute_Value,'MO') 
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Test Max.'
    END
    END

    -- Get default sample size quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Sample_Size,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default sample size quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Sample_Size = Attribute_Value
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Sample Size'
    END

    -- Get default pass size quality specification when one is not available in product spec record.
    IF ISNULL(@Quality_Pass_Size,'') = ''
    BEGIN
    SET @Error_Log_Desc = 'Retrieving default pass size quality specification from test id "' + @Quality_Test_ID + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SELECT @Quality_Pass_Size = Attribute_Value
    FROM Quality_Test_Attribute 
    WHERE Test_ID = @Quality_Test_ID AND Attribute = N'Pass Size'
    END

    -- Create quality record.
    SET @Error_Log_Desc = 'Creating quality record for test id "' + @Quality_Test_ID + '", datetime "' + CAST(@Quality_DateTime AS nvarchar(MAX))-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += '", employee id "' + @Quality_Employee_ID + '", equipment id "' + @Quality_Equip_ID + '", container id "' + @Quality_Container_ID
    SET @Error_Log_Desc += '", work order id "' + @Quality_WO_ID + '", and part no. "' + @Quality_Part_No + '"'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added (End)

    BEGIN TRY-- Revision 1 - Added
    INSERT INTO Quality (Quality_Test_ID, Quality_DateTime, Quality_Employee_ID, Quality_Equip_ID, 
    Quality_Container_ID, Quality_WO_ID, Quality_Part_No, Quality_Min, Quality_Tgt_Min, 
    Quality_Tgt, Quality_Tgt_Max, Quality_Max) 
    VALUES (ISNULL(@Quality_Test_ID,''), ISNULL(@Quality_DateTime,GETDATE()), ISNULL(@Quality_Employee_ID,''), 
    ISNULL(@Quality_Equip_ID,''), ISNULL(@Quality_Container_ID,''), ISNULL(@Quality_WO_ID,''), 
    ISNULL(@Quality_Part_No,''), ISNULL(@Quality_Min,''), ISNULL(@Quality_Tgt_Min,''), ISNULL(@Quality_Tgt,''), 
    ISNULL(@Quality_Tgt_Max,''), ISNULL(@Quality_Max,''))
    END TRY-- Revision 1 - Added

    -- Check for errors.
    /*SET @Error = @@ERROR-- Revision 1 - Removed (Begin)

    IF ISNULL(@Error,0) <> 0
    BEGIN
    GOTO CheckForErrors
    END*/-- Revision 1 - Removed (End)

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot create quality record for test id "' + @Quality_Test_ID + '", datetime "' + CAST(@Quality_DateTime AS nvarchar(MAX))
    SET @Error_Log_Desc += '", employee id "' + @Quality_Employee_ID + '", equipment id "' + @Quality_Equip_ID + '", container id "' + @Quality_Container_ID
    SET @Error_Log_Desc += '", work order id "' + @Quality_WO_ID + '", and part no. "' + @Quality_Part_No + '"'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 7

    -- Exit procedure.
    GOTO Rollback_Transaction
    END CATCH-- Revision 1 - Added (End)

    -- Get newly created quality id.
    EXECUTE Write_Error_Log 'INFORMATION','Retrieving newly created quality id','Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    SELECT @Quality_ID = SCOPE_IDENTITY()
    END TRY-- Revision 1 - Added

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot retrieve newly created quality id' + CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 8

    -- Exit procedure.
    GOTO Rollback_Transaction
    END CATCH-- Revision 1 - Added (End)

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    IF ISNULL(@Error,0) = 0
    BEGIN
    -- Insert test values into quality detail table.
    WHILE @Done = 0 AND @Error = 0
    BEGIN
    SET @Error_Log_Desc = 'Parsing quality values "' + @Quality_Values + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Value = dbo.ParseText(@Quality_Values,',',@Quality_Value_Count)

    IF ISNULL(@Quality_Value,'') = ''
    BEGIN
    SET @Done = 1
    END
    ELSE
    BEGIN
    SET @Error_Log_Desc = 'Creating quality detail record for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '", value count "'-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc += CAST(@Quality_Value_Count AS nvarchar(MAX)) + '", and value "' + @Quality_Value
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added (End)

    BEGIN TRY-- Revision 1 - Added
    INSERT INTO Quality_Detail (Quality_ID,Value_Count,Value)
    VALUES (@Quality_ID,@Quality_Value_Count,@Quality_Value)
    END TRY-- Revision 1 - Added

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot create quality detail record for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '", value count "'
    SET @Error_Log_Desc += CAST(@Quality_Value_Count AS nvarchar(MAX)) + '", and value "' + @Quality_Value
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 9
    END CATCH-- Revision 1 - Added (End)
    END

    -- Add quality value to running total.
    SET @Error_Log_Desc = 'Adding quality value "' + @Quality_Value + '" to running total'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Value_Sum += ISNULL(@Quality_Value,0)

    -- Increment value counter.
    SET @Error_Log_Desc = 'Incrementing value counter "' + CAST(@Quality_Value_Count AS nvarchar(MAX)) + '" by one'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Value_Count += 1
    END

    -- When sample size and pass size are blank or zero.
    IF ISNULL(@Quality_Sample_Size,'') = '' OR ISNULL(@Quality_Sample_Size,0) = 0
    BEGIN
    SET @Error_Log_Desc = 'Setting default sample size to 1'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Sample_Size = 1
    END

    IF ISNULL(@Quality_Pass_Size,'') = '' OR ISNULL(@Quality_Pass_Size,0) = 0
    BEGIN
    SET @Error_Log_Desc = 'Setting default pass size to 1'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Pass_Size = 1
    END

    -- Calculate if test passed or failed.
    IF ISNULL(@Quality_Value_Count,0) < ISNULL(@Quality_Sample_Size,0)
    BEGIN
    SET @Error_Log_Desc = 'Setting test result to FAIL for insufficient sampling'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Result = N'FAIL'
    END
    ELSE
    BEGIN
    IF ISNULL((SELECT COUNT(*) FROM Quality_Detail WHERE Quality_ID = @Quality_ID AND Result = N'PASS'),0) >= ISNULL(@Quality_Pass_Size,0)
    BEGIN
    SET @Error_Log_Desc = 'Setting test result to PASS'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Result = N'PASS'
    END
    ELSE IF ISNULL((SELECT COUNT(*) FROM Quality_Detail WHERE Quality_ID = @Quality_ID AND (Result = N'PASS' OR Result = N'WARNING')),0) >= ISNULL(@Quality_Pass_Size,0)
    BEGIN
    SET @Error_Log_Desc = 'Setting test result to WARNING'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Result = N'WARNING'
    END
    ELSE
    BEGIN
    SET @Error_Log_Desc = 'Setting test result to FAIL'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added
    SET @Quality_Result = N'FAIL'
    END
    END

    IF ISNULL(@Error,0) = 0
    BEGIN
    SET @Error_Log_Desc = 'Updating quality attribute (QIM) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Quality_Attribute
    SET Attribute_Value = dbo.Calculate_QIM(@Quality_Min,@Quality_Tgt,@Quality_Max,(@Quality_Value_Sum/@Quality_Value_Count-1))
    WHERE Quality_ID = @Quality_ID AND Attribute = N'QIM'
    END TRY-- Revision 1 - Added

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update quality attribute (QIM) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 10
    END CATCH-- Revision 1 - Added (End)

    IF ISNULL(@Error,0) = 0
    BEGIN
    SET @Error_Log_Desc = 'Updating quality attribute (Reason) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Quality_Attribute
    SET Attribute_Value = @Quality_Reason
    WHERE Quality_ID = @Quality_ID AND Attribute = N'Reason'
    END TRY-- Revision 1 - Added

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update quality attribute (Reason) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 10
    END CATCH-- Revision 1 - Added (End)

    IF ISNULL(@Error,0) = 0
    BEGIN
    IF ISNULL(@Quality_Comment,'') <> ''
    BEGIN
    SET @Error_Log_Desc = 'Updating quality attribute (Comment) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Quality_Attribute
    SET Attribute_Value = @Quality_Comment
    WHERE Quality_ID = @Quality_ID AND Attribute = N'Comment'
    END TRY-- Revision 1 - Added

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update quality attribute (Comment) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 10
    END CATCH-- Revision 1 - Added (End)
    END
    ELSE
    BEGIN
    SET @Error_Log_Desc = 'Deleting quality attribute (Comment) for quality id "' + CAST(@Quality_ID AS nvarchar(MAX))-- Revision 1 - Added
    SET @Error_Log_Desc += '" as it is blank.'
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    DELETE FROM Quality_Attribute
    WHERE Quality_ID = @Quality_ID AND Attribute = N'Comment'
    END TRY-- Revision 1 - Added

    -- Log error but continue.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot delete quality attribute (Comment) for quality_id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'
    SET @Error_Log_Desc += CHAR(13) + ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality'
    END CATCH-- Revision 1 - Added (End)
    END

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    IF ISNULL(@Error,0) = 0
    BEGIN
    SET @Error_Log_Desc = 'Updating quality result as "' + @Quality_Result + '" for quality id "' + CAST(@Quality_ID AS nvarchar(MAX)) + '"'-- Revision 1 - Added
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'-- Revision 1 - Added

    BEGIN TRY-- Revision 1 - Added
    UPDATE Quality
    SET Result = @Quality_Result
    WHERE Quality_ID = @Quality_ID
    END TRY-- Revision 1 - Added

    -- Check for errors.
    --SET @Error = @@ERROR-- Revision 1 - Removed

    -- Log errors.
    BEGIN CATCH-- Revision 1 - Added (Begin)
    SET @Error_Log_Desc = 'Cannot update quality result as "' + @Quality_Result + '" for quality id "'
    SET @Error_Log_Desc += CAST(@Quality_ID AS nvarchar(MAX)) + '"' + CHAR(13) 
    SET @Error_Log_Desc += ERROR_MESSAGE() + CHAR(13) + ' ERROR CODE:' + CAST(ERROR_NUMBER() AS nvarchar(MAX))
    SET @Error = 11
    END CATCH-- Revision 1 - Added (End)
    END
    END
    END
    END
    END

    Rollback_Transaction:-- Revision 1 - Added (Begin)
    -- When errors are generated during transaction, rollback.
    IF @@ERROR <> 0 OR @Error <> 0
    BEGIN
    IF XACT_STATE() < 0
    BEGIN
    ROLLBACK TRANSACTION
    END

    IF XACT_STATE() > 0
    BEGIN
    ROLLBACK TRANSACTION Capture_Inline_Quality
    END

    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality'
    EXECUTE Write_Error_Log 'INFORMATION','Rolling back Transaction (Capture_Inline_Quality)','Capture_Inline_Quality'
    END

    Commit_Transaction:
    -- Commit transaction.
    EXECUTE Write_Error_Log 'INFORMATION','Committing Transaction (Capture_Inline_Quality)','Capture_Inline_Quality'

    IF XACT_STATE() > 0
    BEGIN
    COMMIT TRANSACTION Capture_Inline_Quality
    END-- Revision 1 - Added (End)

    Check_For_Errors:
    -- When no errors are generated during transaction, set result to SUCCESS.
    /*IF @@ERROR = 0 AND ISNULL(@Error,0) = 0-- Revision 1 - Removed (Begin)
    BEGIN
    SET @SQL_Result = 'SUCCESS'
    END

    -- When errors are generated during transaction, rollback and set result to FAIL.
    ELSE
    BEGIN
    PRINT 'ROLLBACK TRANSACTION - Capture_Inline_Quality'
    ROLLBACK TRANSACTION Capture_Inline_Quality
    SET @SQL_Result = 'FAIL'
    END

    -- Commit transaction and return result.
    PRINT 'COMMIT TRANSACTION - Capture_Inline_Quality'
    COMMIT TRANSACTION Capture_Inline_Quality*/-- Revision 1 - Removed (End)

    IF @Error = 0-- Revision 1 - Added (Begin)
    BEGIN
    SET @SQL_Result = 'SUCCESS'
    END
    ELSE
    BEGIN
    SET @SQL_Result = 'FAIL'
    END

    Log_Result_Before_Exit:
    -- Log result and error code.
    SET @Error_Log_Desc = 'Returning procedure RESULT (' + @SQL_Result + ') and ERROR CODE (' + CAST(@Error AS nvarchar(MAX)) + ')'

    IF @SQL_Result = 'SUCCESS'
    BEGIN
    EXECUTE Write_Error_Log 'INFORMATION',@Error_Log_Desc,'Capture_Inline_Quality'
    END
    ELSE
    BEGIN
    EXECUTE Write_Error_Log 'ERROR',@Error_Log_Desc,'Capture_Inline_Quality',@Error
    END

    Exit_Procedure:
    -- End procedure.
    EXECUTE Write_Error_Log 'INFORMATION','Ending procedure (Capture_Inline_Quality)','Capture_Inline_Quality'-- Revision 1 - Added (End)

    -- Display result and return error code.
    SELECT @SQL_Result
    RETURN @Error

Viewing 15 posts - 16 through 30 (of 56 total)

You must be logged in to reply to this topic. Login to reply