CODEWITHSUNDEEP
sqlsql-servert-sql
Joachim Weiß
Joachim Weiß

Reputation: 226

Case statement with undefined type? or Why date?

I have created a simple table to store name-value-pairs.

create table ifr_nvpairs
(
    name varchar(255) PRIMARY KEY,
    type char(5),
    string varchar(255),
    bit_value bit,
    int_value bigint,
    dec_value decimal(12,2),
    float_value float,
    date_value datetime,
    char_value varchar(255)

);

There is a constraint and triggers to ensure that an 

insert ifr_nvpairs(name,bit_value) values ('bitValue',1);

results in 

bitValue    BIT     1   true    (null)  (null)  (null)  (null)  (null)

I skip those here for brevety and give you some complete inserts:

INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('bitValue', 'BIT  ', '1', true, null, null, null, null, null);
INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('charValue', 'CHAR ', 'Hugo ist nett!', null, null, null, null, null, 'Hugo ist nett!');
INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('dateValue', 'DATE ', '2020-01-13 00:00:00.000', null, null, null, null, '2020-01-13 00:00:00', null);
INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('decValue', 'DEC  ', '8.15', null, null, 8.15, null, null, null);
INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('floatValue', 'FLOAT', '3.14159', null, null, null, 3.14, null, null);
INSERT INTO "ifr_nvpairs" ("name", "type", "string", "bit_value", "int_value", "dec_value", "float_value", "date_value", "char_value") VALUES ('intValue', 'INT  ', '42', null, 42, null, null, null, null);

For convinience of the users I wanted to create a select which returns the value in the stored type.

My approach was

select 
    case type
    when 'BIT' then  bit_value  
    when 'INT' then  int_value  
    when 'DEC' then  dec_value  
    when 'FLOAT' then  float_value
    when 'DATE' then  date_value 
    when 'CHAR' then  char_value
    end result 
    from ifr_nvpairs where name='bitValue';

I wouldn't be surprised if there was an error. But the result is always a datetime.

Why? And is there possible construct in SQL to select an heterogen type?

TIA Jo

Upvotes: 3

Views: 478

Answers (2)

Thom A
Thom A

Reputation: 95561

A CASE expression uses Data type precedence (Transact-SQL) to determine the return data type:

SQL Server uses the following precedence order for data types:

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetimeoffset
  5. datetime2
  6. datetime
  7. smalldatetime
  8. date
  9. time
  10. float
  11. real
  12. decimal
  13. money
  14. smallmoney
  15. bigint
  16. int
  17. smallint
  18. tinyint
  19. bit
  20. ntext
  21. text
  22. image
  23. timestamp
  24. uniqueidentifier
  25. nvarchar (including nvarchar(max) )
  26. nchar
  27. varchar (including varchar(max) )
  28. char
  29. varbinary (including varbinary(max) )
  30. binary (lowest)

The type with the highest precedence will be returned, which in this case, is datetime (with a precedence of 6).

As for your question "And is there possible construct in SQL to select an heterogen type?", as mentioned in the comments, sql_variant does exist, but I recommend against it. It has very few use cases and any comparisons require that the value is explicitly converted. As you can't use syntax like CONVERT(SQL_VARIANT_PROPERTY(YourColumn,'BaseType'),YourColumn) you would have to use dynamic SQL to do this, and you still can't have multiple data types in a single column.

If you need to display different data types, do so in different columns.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A SQL select query returns a fixed set of columns. Each column has a fixed type, determined when the query is compiled.

A case expression returns a single type as well. By the rules of type conversion, that type is generally the "most specific" (these are the rules of SQL). With the plethora of types you have provided, the result of the rules appears to be a datetime.

You cannot return multiple different types in a single column unless you specify the value as a sql_variant (which is really just another type).

You cannot do what you want with regular SQL. One possibility is dynamic SQL. Another would be a different approach that didn't require such logic.

Upvotes: 2

Related Questions