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) &gt; 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) &gt; 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)) &lt;&gt; 1


END
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 &lt;&gt; 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)) &lt;&gt; 1;