From: tfs@dslextreme.com   
      
   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.   
      
   Thanks,   
      
   Tom   
      
   "Hugo Kornelis"  wrote in message   
   news:s1rsd6ldk8vi0bf8tmf4np6e1lhmmqmjt2@4ax.com...   
   > On Fri, 12 Nov 2010 21:13:49 -0800, "tshad"    
   > wrote:   
   >   
   > (snip)   
   >>I thought about changing it to:   
   >>   
   >> SELECT *   
   >>  FROM Image   
   >>  WHERE nUserID = @UserID AND   
   >>                ((@EventID is NULL AND nEventID is null)  OR (@EventID IS   
   >>NOT NULL AND nEventID = @EventID)) AND   
   >>                ((@DocumentID IS NULL AND nDocumentID is null)  OR   
   >>(@DocumentID IS NOT NULL AND nDocumentID = @DocumentID))   
   >>   
   >>if @@ROWCOUNT = 0   
   >>   INSERT ...   
   >>   
   >>Is this the best way to do this?   
   >   
   > Hi Tom,   
   >   
   > Almost. You can slightly simplify the test. More important: the   
   > seperation of SELECT * and INSERT leaves room for problems if a   
   > concurrent connection inserts the same row at almost the same time.   
   >   
   > My suggestion:   
   >   
   > INSERT INTO Image (nUserID, nEventID, nDocumentID, ...)   
   > SELECT @UserID, @EventID, @DocumentID, ...   
   > WHERE NOT EXISTS   
   > (SELECT *   
   >  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));   
   > --   
   > 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)