Cursor replacement suggestions

Question

Cursor replacement suggestions

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

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

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.

Bruin SSCrazy Points: 2058 More actions · Answer 3

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

Bruin SSCrazy Points: 2058 More actions · Answer 4

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

THanks.

Bruin SSCrazy Points: 2058 More actions · Answer 5

Any suggestions?

Thanks I can supply more info if needed..

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

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

Bruin SSCrazy Points: 2058 More actions · Answer 7

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

Thanks.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

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)

Bruin SSCrazy Points: 2058 More actions · Answer 9

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

any suggestions examples

frederico_fonseca SSCoach Points: 16154 More actions · Answer 10

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.

Bruin SSCrazy Points: 2058 More actions · Answer 11

Would it help if I post Capture_Inline_Quality ?

Thanks.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

"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)

Bruin SSCrazy Points: 2058 More actions · Answer 13

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...

ScottPletcher SSC Guru Points: 101096 More actions · Answer 14

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Bruin SSCrazy Points: 2058 More actions · Answer 15

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