Skip to content
Advertisement

Apply multiple validation to SQL records and identify all failing validations

I have a requirement where I need to capture all failing validations in SQL database.

I have below table:

department     age 
IT             27  
IT             25  
Marketing      29
Fashion        30
IT             32
IT             28

My validation rules are as follows:

  1. Department should be IT
  2. Age should be in 20s
  3. Age should not be 25

These are sample validation and my requirement is to capture all failed records along with failing conditions, I have achieved it by using union as follows:

    select department , age , 'Age is 25' as FailedValidation, 1 as FailedValidationId from 
    CENTRALFINANCETAX_IM_DEV.salluTest where age = 25
    union
    select department , age, 'Age is not in 20s' as FailedValidation, 2 as FailedValidationId from 
    CENTRALFINANCETAX_IM_DEV.salluTest where age > 29
    union
    select department , age , 'Dept not IT' as FailedValidation, 3 as FailedValidationId from 
    CENTRALFINANCETAX_IM_DEV.salluTest where department != "IT"

Below is the result achieved:

department  age FailedValidation    FailedValidationId
IT          32  Age is not in 20s   2
Marketing   29  Dept not IT         3
Fashion     30  Dept not IT         3
Fashion     30  Age is not in 20s   2
IT          25  Age is 25           1

But, if the validations increase I have to add many select query.

Is there any alternative way with better performance?

Advertisement

Answer

You could use cross apply:

select t.*, x.*
from centralfinancetax_im_dev.sallutest t
cross apply (values
    (1, case when t.age = 25 then 'Age is 25' end),
    (2, case when t.age > 29 then 'Age is not in 20s' end),
    (3, case when t.department <> 'IT' then 'Dept not IT' end)
) x(FailedValidationId, FailedValidation)
where x.FailedValidation is not null
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement