SQL Server check resultant data type of expression

问题:

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?

回答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?

回答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...

回答3:

I've never seen such a function.

From the MSDN article on T-SQL expressions:

For a simple expression made up of a single constant, variable, scalar function, or column name: the data type, collation, precision, scale, and value of the expression is the data type, collation, precision, scale, and value of the referenced element.

When two expressions are combined by using comparison or logical operators, the resulting data type is Boolean and the value is one of the following: TRUE, FALSE, or UNKNOWN. For more information about Boolean data types, see Comparison Operators (Transact-SQL).

When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.

Complex expressions made up of many symbols and operators evaluate to a single-valued result. The data type, collation, precision, and value of the resulting expression is determined by combining the component expressions, two at a time, until a final result is reached. The sequence in which the expressions are combined is defined by the precedence of the operators in the expression.