From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   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)