Data Driven Type Conversion?

Question

I have a table in SQL Server of key-value pairs along with a field containing the T-SQL data type and other fields to indicate whether a particular field is mandatory, unique, etc. I have written a stored procedure to output a normalised table of data, using the data type field to define the type of each field. This works well. However, before running it, I run another procedure to check for potential errors. One obvious check is: does any data violate the specified type?

I would like to write something like this:

SELECT [key], [value], [type]
FROM [source_table]
WHERE TRY_CONVERT([type], [value]) Is Null AND [value] Is Not Null

which would return the records where the conversion fails (with some exceptions, e.g. int to date). However, this does not work because the first argument of TRY_CONVERT must be a keyword.

They only solution I have found is to produce a block of SQL dynamically which runs once for each datatype to be checked. However, this is irritatingly long winded! I wonder if anyone has found a more succinct way? This must be a common problem.

Dynamic SQL is what you need. Perhaps what you should be considering is changing your design; as you likely are (quickly) finding out EAV design is a pain to work with in a *relational* model. — Thom A, Aug 30 '23 at 10:02
Actually it's not a common problem, because this is not the way a relational database is intended to be used. And as Thom says, it causes many issues. — Dale K, Aug 30 '23 at 10:13
An EAV design is by itself imho neither good or bad, it all depends on if it's the right hammer for your specific nail; why not always validate the data when inserting? — Stu, Aug 30 '23 at 10:16
you can do a case when type when 'int' then try_convert(int, ...) when '...' then ... . But this gets old fast. — siggemannen, Aug 30 '23 at 10:18
I don't implement EAV systems often, but when I do, I prefer to use separate columns for each major type family. I wrote this 14 years ago and probably still how I'd do it today: [What is so bad about EAV, anyway?](https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway) — Aaron Bertrand, Aug 30 '23 at 11:29

score 1 · Answer 1 · answered Aug 30 '23 at 20:15

They only solution I have found is to produce a block of SQL dynamically which runs once for each datatype to be checked. However, this is irritatingly long winded!

Aaron Bertrand's article looks good as a way to manage things with more db-level typing. Your design would still work, and I don't follow why this would be a dealbreaker for you: given your design, it's one of the prices you need to pay.

I might be misunderstanding, but rather than dynamically generating SQL on-the-fly, wouldn't you just create a stored procedure, and validate that your data is covered by the case blocks? Granted, the proc will be long, but again that's not a dealbreaker.

Also, instead of try_convert handling every type, break it down a bit more:

SELECT [key], [value]
FROM [source_table]
WHERE TRY_CONVERT_INT([value]) Is Null AND [value] Is Not Null and [type] = 'int'

union

etc etc.

Presumably the list of 'type' values will be constrained.

score 0 · Answer 2 · answered Aug 30 '23 at 13:38

A Case expression can only return a single data type, so returning the result of Try_Convert for various data types is messy. You can check the result of Try_Convert within the Case to avoid that issue:

declare @Samples as Table ( DT VarChar(32), V VarChar(32) );
insert into @Samples ( DT, V ) values
  ( 'Int', '42' ), ( 'Int', '-13' ), ( 'Int', '0xDEADBEEF' ),
  ( 'Date', '6/7/89' ), ( 'Date', '13/7/89' ), ( 'Date', '6/13/89' ), ( 'Date', '6/7/2' );
select DT, V, Ph.Status
  from @Samples cross apply
    ( select case
        when DT = 'Date' and Try_Convert( Date, V ) is NULL then 'failed'
        when DT = 'Int' and Try_Convert( Int, V ) is NULL then 'failed'
        else '' end as Status ) Ph
--  where Ph.Status != '' -- Uncomment if you only want to output failures.
  ;

dbfiddle.

Data Driven Type Conversion?

2 Answers2