From: reb01501@NOyahoo.SPAMcom   
      
   Of course you are correct. I failed to read it closely enough.   
      
   Hugo Kornelis wrote:   
   > Hi Tom,   
   >   
   > Bob's answer is not completely correct. If you insert the row, the   
   > lock is kept until the end of the transaction. But without locking   
   > hints and under the default transaction isolation level, the lock used   
   > by the SELECT will be released immediately. So your code does have the   
   > risk of getting vioaltions of the unique constraint on (nUserID,   
   > nEventID, nDocumentID).   
   >   
   > Try this instead:   
   >   
   > DECLARE @OutTab TABLE(ImageID int);   
   > DECLARE @ImageID int;   
   > BEGIN TRANSACTION;   
   > BEGIN TRY;   
   >   INSERT INTO Image (columns)   
   >   OUTPUT ImageID INTO @OutTab   
   >   SELECT (columns)   
   >   WHERE NOT EXISTS (see previous post);   
   >   IF @@ROWCOUNT = 0   
   >   BEGIN;   
   >     SELECT @ImageID = ImageID   
   >     FROM   Image   
   >     WHERE  you know what comes here;   
   >   END;   
   >   ELSE   
   >   BEGIN;   
   >     SELECT @ImageID = ImageID   
   >     FROM   @OutTab;   
   >   END;   
   >   -- Other INSERT statements, using @ImageID.   
   >   COMMIT TRAN;   
   > END TRY   
   > BEGIN CATCH;   
   >   ROLLBACK TRAN;   
   >   RAISERROR ('Descriptive error message', 16, 1);   
   > END;   
   >   
   > Yet another possibility is to not use the table variable and the   
   > output clause, and to make the SELECT @ImageID = ... unconditional;   
   > this will either read the just-inserted row, or the row that already   
   > existed and caused the insert to do nothing.   
      
   --   
   HTH,   
   Bob Barrows   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)