SQL Server check resultant data type of expression

Question

Normally I would put the data type into a temp table and inspect the table column type, e.g.

select 1.0 N into tbl  sp_help tbl 

Column N then reveals the data type of the expression 1.0. (This is a only simple example)

There is a SQL function to inspect the data type of an expression directly, but the name escapes me right now.

What is the name of this function?

Answer 1

SQL_VARIANT_PROPERTY is close

DECLARE @what sql_variant; DECLARE @foo decimal(19,3) = 1, @bar decimal(11,7) = 2;  SELECT @what = @foo / @bar; SELECT     SQL_VARIANT_PROPERTY(@what, 'BaseType'),     SQL_VARIANT_PROPERTY(@what, 'Precision'),     SQL_VARIANT_PROPERTY(@what, 'Scale'),     SQL_VARIANT_PROPERTY(@what, 'MaxLength');  SELECT @what = @foo + @bar; SELECT     SQL_VARIANT_PROPERTY(@what, 'BaseType'),     SQL_VARIANT_PROPERTY(@what, 'Precision'),     SQL_VARIANT_PROPERTY(@what, 'Scale'),     SQL_VARIANT_PROPERTY(@what, 'MaxLength');   SELECT @what = @foo * @bar; SELECT     SQL_VARIANT_PROPERTY(@what, 'BaseType'),     SQL_VARIANT_PROPERTY(@what, 'Precision'),     SQL_VARIANT_PROPERTY(@what, 'Scale'),     SQL_VARIANT_PROPERTY(@what, 'MaxLength'); 

Or temp table/SELECT..INTO.. as an extension of what you've already done

Edit: Remus' answer?

Answer 2

All that I can think of are the ISNUMERIC and ISDATE functions.

These will return a 1/0 when passed an expression. I can't think of anything that will return the type if given an expression unfortunately.

UPDATE:

Try SQL_VARIANT_PROPERTY! I think this is what you are looking for. Hard to track down...