From: reb01501@NOSPAMyahoo.com   
      
   tshad wrote:   
   > I do like your simplification better.   
   >   
   > But I still need to do the select first because I need the PK for   
   > another insert.  The key is a FK to another table.  Otherwise yours   
   > is perfect. ImageID is an identity column (don't tell CELKO :) )   
   >   
   > To handle the concurrent connection I would wrap the whole   
   > transaction in a transaction.   
   >   
   > DECLARE @ImageID bigint   
   >   
   > BEGIN TRY   
   >    BEGIN TRAN   
   >        SELECT @ImageID = ImageID   
   >        FROM   Image   
   >        WHERE  nUserID = @UserID   
   >                     AND ( (nEventID IS NULL AND @EventID IS NULL)   
   >                             OR nEventID = @nEventID)   
   >                    AND ( (nDocumentID IS NULL AND @DocumentID IS NULL)   
   >                            OR nDocumentID = @nDocumentID);   
   >   
   >         IF @@ROWCOUNT = 0   
   >         BEGIN   
   >               INSERT INTO Image (nUserID, nEventID, nDocumentID, ...)   
   >               SELECT @UserID, EventID,DocumentID,...)   
   >   
   >              SELECT @ImageID = SCOPE_IDENTITY()   
   >         END   
   >   
   >         INSERT ...   
   >         SELECT @ImageID,...   
   >   
   >        INSERT another table...   
   >   
   >        COMMIT TRAN   
   >    END TRY   
   >    BEGIN CATCH   
   >        ROLLBACK TRAN   
   >    END CATCH   
   > END TRY   
   >   
   > END   
   >   
   > What do you think?   
   >   
   > Also, isn't there a time between the insert/select where someone   
   > could get in or does it lock the record until the whole insert/select   
   > is done?  Wasn't sure on that.  I know the change would be remote.   
   >   
   It would take you five minutes to open a couple query windows and test it to   
   find out .... but yes, the row would be locked until the transaction is   
   committed (or rolled back)   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)