Anticipated PK Violation

I am maintaining a stored proc that does the following (pseudo code for simplicity)

INSERT INTO MyTable (CountColumn, ...) VALUES (1, ...)

-- 2627 = Primary key violation
IF (@.@.ERROR = 2627)
UPDATE MyTable SET CountColumn = CountColumn + 1
WHERE ...

Basically, try to create a new record with a PK and a count of 1. If a PK violation occurs, UPDATE the count of existing record.

Pretty simple logic. On some servers this works exactly as expected; the PK violation is caught and handled internally. On other servers, this error percolates out of the stored proc and causes the calling code to receive an error and fail. All servers are SQL Server 2000 SP3a Standard Edition. Any ideas why this happens?

Is there a better (and ideally as fast or faster) way to handle the INSERT/UPDATE issue? I can put the UPDATE first and INSERT if @.@.ROWCOUNT is 0, but that has the slight potential of a race condition where two processes try to INSERT the same PK at the same time.you could try a conditional flow control block like this:

if not exists (select * from mytable where PK = x)
insert ...
else
update ...

the subquery will always be executed, but it'll be quite fast with the search condition on the primary key

hth,
Cristian Babu|||I'd reverse the if...else:

if exists (...) update... else insert...|||I'd reverse the if...else:

if exists (...) update... else insert...

What I did is:

UPDATE
IF @.@.ROWCOUNT = 0 INSERT

I would think that is slightly more efficient.

What is really odd that the original approach (INSERT, IF ERROR THEN UPDATE) worked fine in Query Analyzer on two servers running SQL Server 2000 Personal edition but produces errors on a new installation of SQL Server 2000 Standard edition. Ideally, as a maintenance programmer, I wouldn't have had to touch the stored proc.|||Are the service pack levels identical on all 3 machines?|||Are the service pack levels identical on all 3 machines?

There is:
SQL Server Personal (no SP): The original code doesn't percolate an error
SQL Server Personal (SP3): The original code doesn't percolate an error
SQL Server Standard (SP3): The original code DOES percolate an error

I'm trying to get coworkers to patch the one unpatched server (we should ALWAYS patch our servers). But either way, it doesn't seem to be related to service patck since two systems with SP3 exhibit different behavior.