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 @activeValidationsselect Validation_ID, 0 from Validations_List where [ENABLED] = 'Y'-- UDTdeclare @keyData KeyDatainsert into @keyDataselect distinct Field1, Field2, Field3, Field4, Field5, Field6, 0 as 'Processed'from [FILE]where Field2 is not nullWHILE (select COUNT(*) from @keyData where processed = 0) > 0BEGIN    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 = @recordIdENDValidation ProcALTER PROCEDURE [dbo].[sp110_v1] (@keyData KeyData READONLY)ASBEGIN    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)) <> 1END
Running validation procedures on a table
sql;validation;sql server;t sql
Iterating row by rowAssuming 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 = 0BEGIN    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;ENDCLOSE validator_cursor;DEALLOCATE validator_cursor;Forget proceduresUsing 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 beINSERT 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;