Data validation and error handling during insertion of data into a temp table bu using Stored Proced

[VIJSQL01]

Hi,

I have a stored procedure in SQL 2008 which does certain calculations based on business rules. In order to do the calculations, we need to supply data in
the form of file in .csv format. As of now, the stored procedure is unable to process the data, if the data file has a header.
The reason being not able to process the data is, SP is unable to insert the data into the temporary table due to confliction of the data type which is defined for the colomn. I mean to say, there is a column called Zip Code and Zip Code is defined as integer. If this column is having integer data, then it will be inserted into the temp table. otherwise it throws an error message saying, data type mismatch and insertion process become infinite.

i have two things in this context.

1. I want to first validate the first row, for the required format of the data. If the data is not in the required format, it should proceed to the next row start the portfolio valuation. It should do the same thing, even when it faces the same situation in the next set of rows in the file.

2.we have error handling in place for the records which are having invalid data IN THE OUTPUT DATA TABLE(not during insertion of the data). In the output table, where the output data will be written has extra columns where in it has to populate exception code and proceed further for valuating the valid records. The same exception is applicable for the first header row also.

Thank you very much for helping on this.

Thanks,
VIJSQL.

 

[drtree]

Hi,

Couldn't you just set up your query so that the the headers are excluded in the where clause? As an example (I tried to set it up to exclude those instances where the Zip Code field is ZipCode too)...

SELECT * 
FROM MyTable
WHERE ZipCode NOT LIKE  '%Zip%Code%'

Alternatively, you could just use the IsNumeric function to see if the value in the Zip Code column is numeric and exclude any columns where it isn't.

Also, are you sure you want the zip code column to be an integer? How would you handle those cases where the zip code starts with a (leading) zero?

HTH

 

[VIJSQL01]

Thank you very much.

Infact i never though about leading zeros in the Zip code. Thank you for reminding me on that. Finally what i did is, i defined the data type as varachar. it solved my purpose.
At the same time, the validation function, the one which you mentioned above, can it be used dynamically when inserting the data?

 

[drtree]

Sure! When you say "used dynamically", what do you mean?