From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   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.   
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)