Then you are going to have a long uphill battle validating all your datatypes. This is truly a nightmare.

Are you planning on validating these values one column at a time? What about processing multiple rows at a time?

Here is an example of just what an absolute nightmare this type of thing is. This will attempt to cast 2 rows in a table like into their "appropriate" datatypes. It requires things like dynamic sql and some trickery to avoid cursors.

create table #nightmare

(

data_value nvarchar(100),

sql_data_type varchar(100)

)

insert #nightmare

select '1900-01-01', 'datetime' union all

select '123.45', 'decimal(5,2)'

declare @sql nvarchar(max) = ''

;with sqlData as

(

select distinct stuff((

select 'cast(''' + data_value + ''' as ' + sql_data_type + '), '

from #nightmare

for xml path('')), 1, 0 , '')

as MyValues

from #nightmare

)

select @sql = LEFT(MyValues, len(MyValues) - 1)

from sqlData

set @sql = 'select ' + @sql

exec sp_executesql @sql

drop table #nightmare

Now the next questions is...how do you validate the sql_data_type column? The short answer, you can't. :w00t: If you have an invalid datatype, this will come to a grinding halt. Even worse is you can't use foreign keys to sys.systypes because you will have to specify the length, scale and precision depending on the datatype.

In short, run away from the type of architecture as fast as you possibly can. It is nothing but painful to work with and will prove to horribly slow once you start adding any amount of data to the system.