Submit Your Suggestions for Vista

Problem

When validating CMAccts while inserting rows into the Vendor Master table, the CMAcct field is incorrectly validated.

Existing logic does a DISTINCT count of valid CMAccts plus unspecified CMAcct lines, then compares that with the total inserted rows. This gives a false positive as in the example below.

Current logic

IF @nullcnt + @validcnt <> @numrows THROW ERROR

Example

Say you are inserting four vendors:

• one with CMAcct = NULL
• two with CMAcct = 20 (valid),
• one with CMAcct = 50 (valid)

That would turn the variables into the following numbers:

• @nullcnt = 1
• @validcnt = 2 (two unique valid CMAccts)
• @numrows = 4

So the formula would look like

IF 1 + 2 <> 4 THROW ERROR

Which would throw the error, even though everything is perfectly valid (NULL is allowed, and the accounts are valid).

I realize the UNIQUE constraint on @validcnt is so that if you share the same CMAcct across companies it removes duplicates. But in doing so it also combines vendors with the same CMAcct without accounting for them.

Doing a distinct count of a unique value in the inserted table, such as [Vendor] or [KeyID], fixes this nicely! 

Old code 😕

select @validcnt = count(distinct(c.CMAcct))from dbo.bCMAC c
join inserted i on c.CMAcct = i.CMAcct

new code (tested and working) 😀

select @validcnt = count(distinct(i.Vendor))from dbo.bCMAC c
join inserted i on c.CMAcct = i.CMAcct