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