Running validation procedures on a table

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, Fi

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;

StackExchange Code Review Q#69518, answer score: 2