Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Anticipated PK Violation

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

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

  2. #2
    Join Date
    Aug 2004
    Posts
    10
    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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd reverse the if...else:

    if exists (...) update... else insert...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by rdjabarov
    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.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are the service pack levels identical on all 3 machines?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by rdjabarov
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •