XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   Dear SQLers:   
      
        Another shot at validation, this time for real, I hope.   
      
        Suppose I have a stored procedure to handle insertions and   
   updates to a database table.  While my front-end will have checked the   
   data somewhat but non-authoritatively, I want the database to check   
   it, too, as the authoritative validator.  I see three types of   
   validation: primary key, foreign key, and non-key data.  These are in   
   decreasing order of importance.   
      
        Is there any way to force the order of the checking?  For   
   example, on an insertion, if the PK already exists, that error is more   
   important than that a Postal Code is in the wrong format.   
      
        One possibility is to not have any constraints for non-key   
   columns but instead implement those checks in the stored procedure.  I   
   suppose that I can do something like:   
             insert the row   
             if insertion fails   
                get PK/FK error details   
                return PK/FK error details   
                done   
             if insertion succeeds   
                non-key error details=validate non-key columns   
                if non-key error   
                   rollback   
                   return non-key error details   
                   done   
             commit   
             return no error   
             done   
   ("done" means execution of this code stops.)  This still does not   
   guarantee that PK checking is done before FK checking.   
      
     1) I would prefer to have the non-key column validation defined as a   
   constraint on the table so that it does not get forgotten.   
      
     2) Order is important.  Is there a documented order or a way to set   
   it?   
      
        What do you do to handle validation?   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)