Check data against informationschema.columns

Go To StackoverFlow.com

0

I need to validate the data in the following table. (Update: in the insert and update trigger of table M)

create table M (Column_Name sysname primary key, FirstValue varchar(8000))

The table has a trigger to ensure that all the values of Column_Name are in (select column_name from information_schema.columns where table_name = 'T').

What's the best way to guarantee that M.FirstValue can be inserted into T without any issue. Is there any third party function accept the parameters of (Value, Data_type, Max_..._length, etc) where the data type information can be provided by information_schema.columns?


** I am trying to ensure that the value in FirstValue can be inserted into the column whose name is stored in the Column_Name column.

2012-04-03 23:19
by ca9163d9
What exactly are you trying to do? Are you trying to ensure that the value in FirstValue can be inserted into the column whose name is stored in the Column_Name column - Pondlife 2012-04-04 09:48
@Pondlife Yes, that's exactly what I am trying to do - ca9163d9 2012-04-04 14:09


1

Thanks for clarifying your question. There's no built-in function or procedure to do that so you'll probably have to write your own. One way would be to try to CAST the value to the same data type as the column. If you get a conversion error, you know an INSERT would fail.

But, there's really no easy and reliable way, because even if the value is compatible with the data type, there may be a CHECK constraint or trigger on the table that prevents it being inserted for some other reason. And since you store all values as strings, there are always potential issues such as conversions to datetime that work fine for you but fail for someone else because of different language or date format options.

2012-04-05 15:08
by Pondlife