Optimization Advice Please!!!

I apologize for the code being lengthy but it's a necessary evil at this point.

I was hoping someone could take a look and give me some pointers on optimizing it.

My first question, in T-SQL if the first ELSE IF is true, does it ignore the following ELSE IF's? If not, how do I escape out of the block?

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

set NOCOUNT ON

GO

ALTERProcedure [dbo].[usp_DockCrewValidation]

@.WeekEnding assmalldatetime,

@.EquipLabor as varchar(20)

AS

DECLARE @.PO as varchar(20),

@.JobClass as varchar(20),

@.PayCode as varchar(20),

@.CostCenter as varchar(20),

@.EmpNum as varchar(20),

@.EquipOrLabor as varchar(20),

@.WorkDate assmalldatetime,

@.ChrgNum as varchar(20),

@.Hours asint,

@.ChrgAmt asmoney,

@.EquipCode as varchar(20),

@.InDCMS asbit,

@.ImportDate assmalldatetime,

@.BlanketNum as varchar(20),

@.EquipNum as varchar(30)

--Disable Update Trigger so it doesn't interfere

ALTERTABLE PManUser.DockCrewImportErrors

DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors asbit

DECLARE DockCrewValidation CURSOR FAST_FORWARD FOR

SELECT PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours, ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum

FROM PManUser.DockCrewImportErrors

WHERECONVERT(char(10), weekending, 101)= @.WeekEnding

AND EquipOrLabor = @.EquipLabor

OPEN DockCrewValidation

FETCH NEXT FROM DockCrewValidation

INTO @.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor, @.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate, @.ImportDate, @.BlanketNum, @.EquipNum

WHILE@.@.FETCH_STATUS= 0

BEGIN

SET @.Errors = 0

IF(SELECT CancelDt FROM RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8))ISNOTNULL

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Job Number Cancelled'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum

SET @.Errors = 1

END

ELSEIF(SELECT zCodeDt FROM RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8))ISNOTNULL

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Job Number Z-Coded'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum

SET @.Errors = 1

END

ELSEIF(SELECT PO FROM PO WHERE PO = @.PO)=''

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid PO Number'

WHERE PONum = @.PO

SET @.Errors = 1

END

ELSEIF(SELECT JobClass FROM JobClass WHERE JobClass = @.JobClass)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Job Class'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

Class = @.JobClass

END

ELSEIF(SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid PayCode'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

Paycode = @.PayCode

SET @.Errors = 1

END

ELSEIF(SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter)=''

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Cost Center'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

CostCenter = @.CostCenter

SET @.Errors = 1

END

ELSEIF(SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum)=''AND @.EquipOrLabor ='Labor'

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Invalid Employee Number'

WHERE PONum = @.PO AND

ChargeNum = @.ChrgNum AND

EmpNum = @.EmpNum

SET @.Errors = 1

END

--End Labor Check

IF @.Errors = 0 --If there are no errors, insert into LaborEntrys Table

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Valid Entry'

WHERE PONum = @.PO

AND EmpNum = @.EmpNum

AND Class = @.JobClass

AND PayCode = @.PayCode

AND CostCenter = @.CostCenter

AND EquipOrLabor = @.EquipOrLabor

AND InDCMS = @.InDCMS

--AND Reason NOT IN ('Job Number Cancelled', 'Job Number Z-Coded')

IF @.EquipORLabor ='Labor'AND @.InDCMS = 1 AND @.BlanketNum = 0

BEGIN

INSERTINTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)

VALUES(@.PO, @.EmpNum, @.WorkDate, @.ChrgNum, @.CostCenter, @.Hours, @.PayCode, @.JobClass, @.ChrgAmt, @.ImportDate, @.WeekEnding)

END

IF @.EquipORLabor ='Equipment'AND @.InDCMS = 1 AND @.BlanketNum = 0

BEGIN

INSERTINTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)

VALUES(@.PO, @.WorkDate, @.ChrgNum, @.EquipCode, @.CostCenter, @.Hours,GETDATE(), @.EquipNum, @.WeekEnding)

END

END

FETCH NEXT FROM DockCrewValidation

INTO @.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor, @.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate, @.ImportDate, @.BlanketNum, @.EquipNum

END

CLOSE DockCrewValidation

DEALLOCATE DockCrewValidation

DELETEFROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'AND InDCMS = 1

ALTERTABLE PManUser.DockCrewImportErrors

ENABLE TRIGGER trg_UpdateValidation

Thanks in advance,

Adamus

Without checking the procedure...

T-SQL does NOT have ELSEIF. Use nested IF...ELSE, or CASE structures.

Do you want to revise and start over?

Sorry Adam, I just had to jerk your chain a little ...

Since you have nested the IF statements, you are the one to determine if the code will flow as you intended.

Good and rigorous formatting usually helps follow the flow...

I think that this can be revised using CASE statements seveal other ways to enhance performance and readibility. I'll get back to you shortly with some suggestions.

Code Snippet

IF (SELECT CancelDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Cancelled'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE
IF (SELECT zCodeDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Z-Coded'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE
IF (SELECT PO FROM PO WHERE PO = @.PO) = ''
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid PO Number'
WHERE PONum = @.PO
SET @.Errors = 1
END
ELSE
IF (SELECT JobClass FROM JobClass WHERE JobClass = @.JobClass) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Job Class'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Class = @.JobClass
END
ELSE
IF (SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid PayCode'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Paycode = @.PayCode
SET @.Errors = 1
END
ELSE
IF (SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter) = ''
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Cost Center'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
CostCenter = @.CostCenter
SET @.Errors = 1
END
ELSE
IF (SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Invalid Employee Number'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
EmpNum = @.EmpNum
SET @.Errors = 1
END

|||

Arnie Rowland wrote:

Without checking the procedure...

T-SQL does NOT have ELSEIF. Use nested IF...ELSE, or CASE structures.

Do you want to revise and start over?

Sorry Adam, I just had to jerk your chain a little ...

Since you have nested the IF statements, you are the one to determine if the code will flow as you intended.

Good and rigorous formatting usually helps follow the flow...

Also, in seveal places, you have a condition test in the form of

IF @.a = @.b IS NOT NULL

It needs to be either @.a = @.b or @.a IS NOT NULL. You can't combine the conditions in this manner, you need to use AND/OR.

I did the best I could to format the code inside that little code window but it should read:

IF (SELECT @.a FROM myTable WHERE @.a = @.b) IS NOT NULL

Also, if I could take a different approach that would optimize performance I'm all ears.

Let me explain a little more.

This stored procedure is called after records are imported into table from Excel. All records begin as errors until proven innocent. It basically grabs one record at a time (hence the cursor) and validates the record through the given code. If the record passes all interrogations, it's inserted into the appropriate table. If it fails, the Reason field is updated and the record remains in the error table.

In all actuality, the first import and query takes around 1 minute to run. The second import and query takes about 2 1/2 minutes to run. This is acceptable, but I was hoping to get it down to under a minute if possible.

Adamus

|||What you posted is about as good as it will get. Yes, SQL will NOT process the other ELSE statements after it hits one which is true.

I would change one minor thing, instead of running UPDATE on every error, I would create a var with the error and do:

IF ..........
BEGIN
SET @.msg = 'Invalid Employee Number'
SET @.ERROR = 1
END
ELSE IF....

IF @.ERROR=0
.....
ELSE
BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason = @.msg

END

You could do it without a cursor by doing:

UPDATE

PManUser.DockCrewImportErrors

SET Reason = 'Job Number Cancelled'

FROM PManUser.DockCrewImportErrors

JOIN RCDCD10 WHERELEFT(JobNo, 8)=LEFT(REPLACE(@.ChrgNum,'-',''), 8)

WHERECONVERT(char(10), weekending, 101)= @.WeekEnding

AND EquipOrLabor = @.EquipLabor

AND Reason IS NULL

AND CancelDT IS NOT NULL

Do this logic again for the next result. This way you are ONLY processing the records which do not have a Reason code AND have the error you are looking for.

|||

Code Snippet

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
set NOCOUNT ON
GO
ALTER Procedure [dbo].[usp_DockCrewValidation]
@.WeekEnding as smalldatetime,
@.EquipLabor as varchar(20)
AS
BEGIN
DECLARE
@.PO as varchar(20),
@.JobClass as varchar(20),
@.PayCode as varchar(20),
@.CostCenter as varchar(20),
@.EmpNum as varchar(20),
@.EquipOrLabor as varchar(20),
@.WorkDate as smalldatetime,
@.ChrgNum as varchar(20),
@.Hours as int,
@.ChrgAmt as money,
@.EquipCode as varchar(20),
@.InDCMS as bit,
@.ImportDate as smalldatetime,
@.BlanketNum as varchar(20),
@.EquipNum as varchar(30)

declare @.ImportErrors table
(
PONum varchar(20),
Class varchar(20),
PayCode varchar(20),
CostCenter varchar(20),
EmpNum varchar(20),
EquipOrLabor varchar(20),
InDCMS bit,
Hours int,
ChrgAmt money,
ChargeNum varchar(20),
EquipCode varchar(20),
WorkDate datetime,
ImportDate datetime,
BlanketNum varchar(20),
EquipNum varchar(30)
)
--Disable Update Trigger so it doesn't interfere

ALTER TABLE PManUser.DockCrewImportErrors
DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors as bit

DECLARE DockCrewValidation CURSOR FAST_FORWARD FOR
select
PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum
From
@.ImportErrors

insert into @.ImportErrors
(PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum)
SELECT
PONum, Class, PayCode, CostCenter, EmpNum, EquipOrLabor, InDCMS, Hours,
ChrgAmt, ChargeNum, EquipCode, WorkDate, ImportDate, BlanketNum, EquipNum
FROM
PManUser.DockCrewImportErrors
WHERE
CONVERT(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor

OPEN DockCrewValidation
FETCH NEXT FROM DockCrewValidation
INTO
@.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor,
@.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate,
@.ImportDate, @.BlanketNum, @.EquipNum

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Errors = 0

IF (SELECT CancelDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Job Number Cancelled'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE IF (SELECT zCodeDt FROM RCDCD10 WHERE LEFT(JobNo, 8) = LEFT(REPLACE(@.ChrgNum, '-',''), 8)) IS NOT NULL
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Job Number Z-Coded'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum
SET @.Errors = 1
END
ELSE IF (SELECT PO FROM PO WHERE PO = @.PO) = ''
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid PO Number'
WHERE PONum = @.PO
SET @.Errors = 1
END
ELSE IF exists (SELECT 'x' FROM JobClass WHERE JobClass = @.JobClass and JobClass = '') AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid Job Class'
WHERE PONum = @.PO
AND ChargeNum = @.ChrgNum
AND Class = @.JobClass
END
ELSE IF (SELECT PayCode FROM PayCodes WHERE PayCode = @.PayCode) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid PayCode'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
Paycode = @.PayCode
SET @.Errors = 1
END
ELSE IF (SELECT CC_Num FROM CostCenter WHERE CC_Num = @.CostCenter) = ''
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Invalid Cost Center'
WHERE PONum = @.PO AND
ChargeNum = @.ChrgNum AND
CostCenter = @.CostCenter

SET @.Errors = 1
END

ELSE IF (SELECT EmpNum FROM Employees WHERE EmpNum = @.EmpNum) = '' AND @.EquipOrLabor = 'Labor'
BEGIN
UPDATE
@.ImportErrors
SET
Reason = 'Invalid Employee Number'
WHERE
PONum = @.PO AND
ChargeNum = @.ChrgNum AND
EmpNum = @.EmpNum

SET @.Errors = 1

END

--End Labor Check

IF @.Errors = 0 --If there are no errors, insert into LaborEntrys Table
BEGIN
UPDATE @.ImportErrors
SET Reason = 'Valid Entry'
WHERE PONum = @.PO
AND EmpNum = @.EmpNum
AND Class = @.JobClass
AND PayCode = @.PayCode
AND CostCenter = @.CostCenter
AND EquipOrLabor = @.EquipOrLabor
AND InDCMS = @.InDCMS

--AND Reason NOT IN ('Job Number Cancelled', 'Job Number Z-Coded')

IF @.EquipORLabor = 'Labor' AND @.InDCMS = 1 AND @.BlanketNum = 0
BEGIN
INSERT INTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)
VALUES(@.PO, @.EmpNum, @.WorkDate, @.ChrgNum, @.CostCenter, @.Hours, @.PayCode, @.JobClass, @.ChrgAmt, @.ImportDate, @.WeekEnding)
END

IF @.EquipORLabor = 'Equipment' AND @.InDCMS = 1 AND @.BlanketNum = 0
BEGIN
INSERT INTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)
VALUES(@.PO, @.WorkDate, @.ChrgNum, @.EquipCode, @.CostCenter, @.Hours, GETDATE(), @.EquipNum, @.WeekEnding)
END
END

FETCH NEXT FROM DockCrewValidation
INTO
@.PO, @.JobClass, @.PayCode, @.CostCenter, @.EmpNum, @.EquipOrLabor,
@.InDCMS, @.Hours, @.ChrgAmt, @.ChrgNum, @.EquipCode, @.WorkDate,
@.ImportDate, @.BlanketNum, @.EquipNum

END
CLOSE DockCrewValidation
DEALLOCATE DockCrewValidation
Update
b
Set
Reason = a.Reason
From
PManUser.DockCrewImportErrors b
Join
@.ImportErrors a
on
b.PONum = a.PONum

DELETE FROM PManUser.DockCrewImportErrors
WHERE
Reason = 'Valid Entry'
AND InDCMS = 1

ALTER TABLE PManUser.DockCrewImportErrors
ENABLE TRIGGER trg_UpdateValidation
END

-- I appreciate if revert back with your comments.
-- I request your to bear with the compilations error(if any)
-- But it would definitely improve the performance
-- I have assumed the PONUM is unique in PManUser.DockCrewImportErrors table. If not please ignore this.

|||

PONum has duplicates. The uniqueness is fairly concatenated as you'll notice in the final UPDATE:

UPDATE @.ImportErrors
SET Reason = 'Valid Entry'
WHERE PONum = @.PO
AND EmpNum = @.EmpNum
AND Class = @.JobClass
AND PayCode = @.PayCode
AND CostCenter = @.CostCenter
AND EquipOrLabor = @.EquipOrLabor
AND InDCMS = @.InDCMS

I will test this on Monday, but I like what I see.

I will definately post a response.

Thank you all,

Adamus

|||

Tom,

I'm not sure I could avoid the cursor considering each check is unique in that it bounces checks from different tables. I do like the idea of segregating the error messages for readability though.

Thanks for your input,

Adamus

|||

Adam,

I'm going to offer a 'best guess' about set based operations. I'm handicapped by not having the DDL and complete understanding of your objectives, but I think you just might be able to handle this with a series of carefully created update queries -and completely avoid the CURSOR. If it could work, the speed difference should be substaintial.

So look this over and tell me what I missed in handling the UPDATE for just these two cases. (Imagine a single statement handling all rows that meet a set of criteria -in one action.)

If you think this has possibilites, I'll help you put the rest together.

Code Snippet

UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Cancelled'
FROM PManUser.DockCrewImportErrors d
JOIN RCDCD10 r
ON ( left(r.JobNo, 8) = LEFT(REPLACE(d.ChrgNum, '-',''), 8)
AND r.CancelDt IS NOT NULL
)
WHERE ( convert(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor
)

IF ( @.@.ROWCOUNT > 0 )
SET @.Errors = 1

UPDATE PManUser.DockCrewImportErrors
SET Reason = 'Job Number Z-Coded'
FROM PManUser.DockCrewImportErrors d
JOIN RCDCD10 r
ON ( left(r.JobNo, 8) = LEFT(REPLACE(d.ChrgNum, '-',''), 8)
AND r.zCodeDt IS NOT NULL
)
WHERE ( convert(char(10), weekending, 101) = @.WeekEnding
AND EquipOrLabor = @.EquipLabor
)

IF ( @.@.ROWCOUNT > 0 )
SET @.Errors = 1

|||

Arnie, it looks like you've struck oil with this one. Let's bring the troops home. We no longer need to import. Let the Suni's and Shiites fight away. lol

I could very well code it in this linear fashion and avoid the cursor altogether. I'm not sure why I was so stubborn to begin with.

The JOIN on the two above will work. The additional blocks will need some tweaks but I believe a LEFT or RIGHT JOIN on the table being bounced should work beautifully.

I'll let you know on Monday.

Thanks again,

Adamus

|||

Arnie,

I worked beautifully.

Both imports and queries took 1.5 minutes and 2.5 minutes. Removing the cursor and using linear updates takes under 1 second.

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTERProcedure [dbo].[usp_DockCrewValidation]

@.WeekEnding assmalldatetime,

@.EquipLabor as varchar(20)

AS

--Disable Update Trigger so it doesn't interfere

ALTERTABLE PManUser.DockCrewImportErrors

DISABLE TRIGGER trg_UpdateValidation

DECLARE @.Errors asbit

SET @.Errors = 0

UPDATE dc

SET dc.InDCMS = 1

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

UPDATE dc

SET dc.Reason ='Job Number Cancelled'

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

AND r.CancelDt ISNOTNULL

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Job Number Z-Coded'

FROM PManUser.DockCrewImportErrors dc

JOIN RCDCD10 r ONLEFT(r.JobNo, 8)=LEFT(REPLACE(dc.ChargeNum,'-',''), 8)

AND r.ZCodeDt ISNOTNULL

WHERE WeekEnding = @.WeekEnding AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid PO Number'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN PO ON dc.PONum = PO.PO

WHERE PO.PO =''

AND WeekEnding = @.WeekEnding

AND EquipOrLabor = @.EquipLabor

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Job Class'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN JobClass jc ON dc.Class = jc.JobClass

WHERE jc.JobClass =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid PayCode'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN PayCodes p ON dc.Paycode = p.Paycode

WHERE p.PayCode =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Cost Center'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN CostCenter c ON dc.CostCenter = c.CC_Num

WHERE c.CC_Num =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

UPDATE dc

SET dc.Reason ='Invalid Employee Number'

FROM PManUser.DockCrewImportErrors dc

LEFTJOIN Employees e ON dc.EmpNum = e.EmpNum

WHERE e.EmpNum =''

AND dc.EquipOrLabor = @.EquipLabor

AND dc.Weekending = @.WeekEnding

IF(@.@.ROWCOUNT> 0 )

SET @.Errors = 1

-End Validation Check-

IF @.Errors = 0 --If there are no errors, insert into associated Table

BEGIN

UPDATE PManUser.DockCrewImportErrors

SET Reason ='Valid Entry'

WHERE Reason =''

INSERTINTO LaborEntrys(PONum, EmpNum, WorkDate, ChrgNum, CostCenter, Hours, PayCode, Class, ChrgAmt, InDate, WeekEnding)

SELECT PONum, Empnum, WorkDate, ChargeNum, CostCenter, Hours, Paycode, Class, ChrgAmt, ImportDate, @.WeekEnding

FROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'

AND EquipOrLabor ='Labor'

AND InDCMS = 1

AND BlanketNum = 0

INSERTINTO EquipEntrys(PONum, WorkDate, ChrgNum, EquipCode, CostCenter, Hours, InDate, EquipNum, WeekEnding)

SELECT PONum, WorkDate, ChargeNum, EquipCode, CostCenter, Hours, ImportDate, EquipNum, @.Weekending

FROM PManUser.DockCrewImportError

WHERE Reason ='Valid Entry'

AND EquipOrLabor ='Equipment'

AND InDCMS = 1

AND BlanketNum = 0

END

DELETEFROM PManUser.DockCrewImportErrors

WHERE Reason ='Valid Entry'AND InDCMS = 1

ALTERTABLE PManUser.DockCrewImportErrors

ENABLE TRIGGER trg_UpdateValidation

Thanks again,

Adamus

|||

I'm glad it worked out for the better -and I'm glad that I could help.

Set based operations are almost always orders of magnitude faster than using CURSORs. But it takes revising our thinking -so much of our application development experience has been centered around handling a single row of data at a time. I know it took me awhile to get to be comfortable with using Set based operations as my first line of thought.