title: Checking whether conditions are met by all rows with dynamic SQL
body: <p>I have a table in SQL Server 2008 which contains custom validation criteria in the form of expressions stored as text, e.g.</p>
  
  <pre><code>StagingTableID    CustomValidation
  ----------------------------------
  1                 LEN([mobile])&lt;=30
  3                 [Internal/External] IN ('Internal','External')
  3                 ([Internal/External] &lt;&gt; 'Internal') OR (LEN([Contact Name])&lt;=100)
  ...
  </code></pre>
  
  <p>I am interested in determining whether all rows in a table pass the conditional statement. For this purpose I am writing a validation stored procedure which checks whether all values in a given field in a given table meet the given condition(s). SQL is not my forte, so after reading <a href="https://stackoverflow.com/questions/925195/select-all-records-dont-meet-certain-conditions-in-a-joined-table">this questions</a> this is my first stab at the problem:</p>
  
  <pre><code>EXEC sp_executesql N'SELECT @passed = 0 WHERE EXISTS (' +
                       N'SELECT * FROM (' +
                         N'SELECT CASE WHEN ' + @CustomValidationExpr + N' THEN 1 ' +
                         N'ELSE 0 END AS ConditionalTest ' +
                         N'FROM ' + @StagingTableName +
                       N')t ' +
                       N'WHERE t.ConditionalTest = 0)'
                    ,N'@passed BIT OUTPUT'
                    ,@passed = @PassedCustomValidation OUTPUT
  </code></pre>
  
  <p>However, I'm not sure if the nested queries can be re-written as one, or if there is an entirely better way for testing for validity of all rows in this scenario?</p>
  
  <p>Thanks in advance!</p>