Running validation procedures on a table

Question

I am working on a project that has to parse through a text file and perform validations on certain fields in the file. Some of the validations are basic (i.e. data types), and other's are a little more complex (i.e. field 1 must be x if field 2 is y).

There is a requirement that this be done in SQL Server. I have a C# background and find myself frustrated by T-SQL's difficulty in manipulating data. It is awesome for Set based operations and CRUD. However, if I need to perform row operations, I find myself writing ugly code. For example, if I need to loop through a table and step through row by row, and pull out 10 columns etc. it generates hard to read code.

Here is the code I have created and with some more information. I have a file with a predetermined format. This file needs to be validated against a list of validations stored in a table. For example, the first character of a line must be either 1, 3 or 5. I need to group the lines together based on a key and then loop through these and run all the validations against them. All this logic must be done in SQL Server.

declare @activeValidations table
(
    validationId int,
    processed bit
) 

insert into @activeValidations
select Validation_ID, 0 from Validations_List where [ENABLED] = 'Y'

-- UDT
declare @keyData KeyData

insert into @keyData
select distinct Field1, Field2, Field3, Field4, Field5, Field6, 0 as 'Processed'
from [FILE]
where Field2 is not null

WHILE (select COUNT(*) from @keyData where processed = 0) > 0
BEGIN
    declare @recordId bigint
    select top 1 @recordId = KeyDataRecordId from @keyData where processed = 0

    update @activeValidations
    set Processed = 0

    WHILE (select COUNT(*) from @activeValidations where processed = 0) > 0
    BEGIN
        declare @validationId int

        select top 1 @validationId = validationId from @activeValidations where processed = 0

        declare @keyDataRow KeyData
        insert into @keyDataRow
        select top 1 Field1, Field2, Field3, Field4, Field5, Field6, [Processed] from @keyData where KeyDataRecordId = @recordId

        declare @storedProcIndicator char(1)
        select @storedProcIndicator = PROC_INDICATOR from Validations_List where Validation_ID = @validationId

        IF @storedProcIndicator = 'Y'
        BEGIN
            declare @procName nvarchar(40)
            select @procName = PROC_NAME from Validations_List where Validation_ID = @validationId

            EXECUTE sp_executesql @procName, N'@keyDataRow KeyData READONLY', @keyDataRow

        END

        update @activeValidations
        set Processed = 1
        where validationId = @validationId
    END

    update @keyData
    set Processed = 1
    where KeyDataRecordId = @recordId
END

Validation Proc

ALTER PROCEDURE [dbo].[sp110_v1] 
(@keyData KeyData READONLY)
AS
BEGIN

    declare @validationMessage varchar(500)
    select @validationMessage = Message from Validations_List where Validation_ID = 110

    -- Field must be numeric in Position 108-117 10 bytes
    insert into FailedValidations
    select Field1, Field2, Field3, Field4, Field5, Field6, @validationMessage
    from [FILE] wf
    where wf.Field1= (select Field1 from @keyData) and
          wf.Field2 = (select Field2 from @keyData) and
          wf.Field3 = (select Field3 from @keyData) and
          wf.Field4 = (select Field4 from @keyData) and
          wf.Field5 = (select Field5 from @keyData) and
          wf.Field6 = (select Field6 from @keyData) and
          wf.RecordType = '04' and
          --Note the row column is varchar(500) and represents 1 row in a text file
          ISNUMERIC(SUBSTRING(Row, 108, 10)) <> 1


END

Answer 1

Iterating row by row

Assuming that you want to perform the validations on the files row by row, you shouldn't be using a temporary processed column to keep track of your progress. The feature you should be using is cursors.

DECLARE validator_cursor CURSOR FOR
    SELECT PROC_NAME
        FROM Validations_List
        WHERE [ENABLED] = 'Y' AND [PROC_INDICATOR] = 'Y';
OPEN validator_cursor;

FETCH NEXT FROM validator_cursor INTO @procName;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE file_cursor CURSOR FOR
        SELECT DISTINCT Field1, Field2, Field3, Field4, Field5, Field6
            FROM [FILE]
            WHERE Field2 IS NOT NULL;
    OPEN file_cursor;

    FETCH NEXT FROM file_cursor INTO @file;
    WHILE @@FETCH_STATUS <> 0
    BEGIN
        EXECUTE sp_executesql @procName, N'@keyDataRow KeyData READONLY', @file;
        FETCH NEXT FROM file_cursor INTO @file;
    END

    CLOSE file_cursor;
    DEALLOCATE file_cursor;

    FETCH NEXT FROM validator_cursor INTO @procName;
END

CLOSE validator_cursor;
DEALLOCATE validator_cursor;

Forget procedures

Using procedures is still a poor approach, though. SQL is designed to be used to work with data sets, operating on a significant portions of a table at a time. Instead, I see a lot of one-value-at-a-time operations, in which you are working very hard to defeat the power and the purpose of SQL.

For example, the sp110_v1 validation should just be

INSERT INTO FailedValidations
    SELECT DISTINCT Field1, Field2, Field3, Field4, Field5, Field6, vl.Message
        FROM [FILE] wf, Validations_List vl
        WHERE
            vl.Validation_ID = 110
            AND vl.[ENABLED] = 'Y'
            AND wf.Field2 IS NOT NULL
            AND wf.RecordType = '04'
            AND ISNUMERIC(SUBSTRING(wf.Row, 108, 10)) <> 1;

Answer 2

The code feels complicated because you are doing unusual things, and it is unusual because you aren't following standard database practices.

Enforcing clean data

Part of the database server's job is to help you maintain the integrity of your data. The best strategy is to ensure that invalid data can never get stored into the database in the first place. To that end, there are built-in validation features that you can take advantage of, namely primary keys, unique constraints, foreign key constraints, and CHECK constraints.

Your sp110_v1 validation procedure could be accomplished with:

ALTER TABLE [FILE] ADD CONSTRAINT If_RecordType_04_then_numeric_position_108_to_117
    CHECK NOT (RecordType = '04' AND ISNUMERIC(SUBSTRING(Row, 108, 10)) <> 1);

… though I think this is a bit easier to understand:

ALTER TABLE [FILE] ADD CONSTRAINT If_RecordType_04_then_numeric_position_108_to_117
    CHECK RecordType <> '04' OR ISNUMERIC(SUBSTRING(Row, 108, 10));