CASE Statement in SQL

just a curious question. I have written following code using CASE statement but I don’t have data so I am not sure if my logic is correct. All I am doing is I am applying length check on each field and if it doesn’t match then put it in error table. So, can anybody just tell me if my logic is correct?

Query:

 INSERT INTO
                [Elig].[dbo].[ErrorTable]
                (
                 [SeqId],
                 [CodeId],
                 [SubjectArea],
                 [FieldName],
                 [TableName],
                 [ErrorValue],
                 [ActiveFlag]
                )
                SELECT
                    [sd].[SuscriberDataId] AS [SeqId],
                    @InvalidLength AS [CodeId],
                    @SubjectArea AS [SubjectArea],
            CASE WHEN len(ltrim(Rtrim([sd].[SubscriberNumber]))) > 9 THEN 'SubscriberNumber'
                 WHEN len(ltrim(Rtrim([sd].[SubscriberLastName]))) > 35 THEN 'SubscriberLastName'
                 WHEN len(ltrim(Rtrim([sd].[SubscriberFirstName]))) > 15 THEN 'SubscriberFirstName'
                 WHEN len(ltrim(Rtrim([sd].[SubscriberMiddleInitial]))) > 1 THEN 'SubscriberMiddleInitial'  
                 WHEN len(ltrim(Rtrim([sd].[Sex]))) > 1 THEN 'Sex'
                 WHEN len(ltrim(Rtrim([sd].[Dob]))) > 8 THEN 'Dob'   
                 WHEN len(ltrim(Rtrim([sd].[Ssn]))) > 9 THEN 'Ssn'
                 WHEN len(ltrim(Rtrim([sd].[FacetsGroup]))) > 8 THEN 'FacetsGroup'  
           END AS FieldName,
           'Eligibility.SubscriberData',
           CASE  WHEN len(ltrim(Rtrim([sd].[SubscriberNumber]))) > 9 THEN [SubscriberNumber]
                 WHEN len(ltrim(Rtrim([sd].[SubscriberLastName]))) > 35 THEN [SubscriberLastName]
                 WHEN len(ltrim(Rtrim([sd].[SubscriberFirstName]))) > 15 THEN [SubscriberFirstName]
                 WHEN len(ltrim(Rtrim([sd].[SubscriberMiddleInitial]))) > 1 THEN [SubscriberMiddleInitial]  
                 WHEN len(ltrim(Rtrim([sd].[Sex]))) > 1 THEN [Sex]
                 WHEN len(ltrim(Rtrim([sd].[Dob]))) > 8 THEN [Dob]   
                 WHEN len(ltrim(Rtrim([sd].[Ssn]))) > 9 THEN [Ssn]
                 WHEN len(ltrim(Rtrim([sd].[FacetsGroup]))) > 8 THEN [FacetsGroup]  
           END AS ErrorValue,           
           @ActiveFlag AS [ActiveFlag]
           FROM [Eligibility].[SubscriberData]
           WHERE len(ltrim(Rtrim([sd].[SubscriberNumber]))) > 9 OR
                 len(ltrim(Rtrim([sd].[SubscriberLastName]))) > 35 OR
                 len(ltrim(Rtrim([sd].[SubscriberFirstName]))) > 15 OR
                 len(ltrim(Rtrim([sd].[SubscriberMiddleInitial]))) > 1 OR  
                 len(ltrim(Rtrim([sd].[Sex]))) > 1 OR
                 len(ltrim(Rtrim([sd].[Dob]))) > 8 OR   
                 len(ltrim(Rtrim([sd].[Ssn]))) > 9 OR
                 len(ltrim(Rtrim([sd].[FacetsGroup]))) > 8 

Answer

Ideally, your source table should be defined so that each field cannot be larger than the defined maximum. I am therefore guessing (hoping) that this is a bit of a one-off exercise to identify bad data and fix it, before fixing the underlying table definition.

As @Matt commented, lots of len(ltrim(rtrim( in here, which is going to make this slow.

And as @Arvo points out, you will only log the first error in each row – e.g. if SubscriberNumber and SubscriberLastName are both too long, only SubscriberNumber will get logged.

You should ensure your data is trimmed when writing it. This will remove the need to ltrim(rtrim( all over the shop. If necessary, run a one-off query across your table to fix the existing data values.

Your where clause is probably as efficient as it’s going to be (once you lose all those trims), and could be used either to drive a cursor or to populate a temp table that you can then query several times – once for each field check.

The latter solution might look like this. First get the rows that have errors:

SELECT
     [sd].[SuscriberDataId]
    ,[sd].[SubscriberNumber]
    ,[sd].[SubscriberLastName]
    ,[sd].[SubscriberFirstName]
    ,[sd].[SubscriberMiddleInitial]
    ,[sd].[Sex]
    ,[sd].[Dob]
    ,[sd].[Ssn]
    ,[sd].[FacetsGroup]
INTO #temp
FROM [Eligibility].[SubscriberData] [sd]
WHERE
    len([sd].[SubscriberNumber]) > 9 OR
    len([sd].[SubscriberLastName]) > 35 OR
    len([sd].[SubscriberFirstName]) > 15 OR
    len([sd].[SubscriberMiddleInitial]) > 1 OR  
    len([sd].[Sex]) > 1 OR
    len([sd].[Dob]) > 8 OR   
    len([sd].[Ssn]) > 9 OR
    len([sd].[FacetsGroup]) > 8 

Then, for one field at a time, get the rows which break the rules:

-- SubscriberNumber check
INSERT INTO [Elig].[dbo].[ErrorTable]
    (
        [SeqId],
        [CodeId],
        [SubjectArea],
        [FieldName],
        [TableName],
        [ErrorValue],
        [ActiveFlag]
    )

SELECT
    [t].[SuscriberDataId],
    @InvalidLength,
    @SubjectArea,
    'SubscriberNumber',
    'Eligibility.SubscriberData',
    [t].[SubscriberNumber],
    @ActiveFlag
FROM #temp
WHERE len([t].[SubscriberNumber]))) > 9

Repeat for the next field, etc.