SQL_VARIANT_PROPERTY

Transact-SQL Reference

Transact-SQL Reference

SQL_VARIANT_PROPERTY

Returns the base data type and other information about a sql_variant value.

Syntax

SQL_VARIANT_PROPERTY ( expression, property )

Arguments

expression

Is an expression of type sql_variant.

property

Contains the name of the sql_variant property for which information is to be provided. property is varchar(128), and can be any of the following values.

Value Description Base type of sql_variant returned
BaseType The SQL Server data type, such as:

char
int

money
nchar
ntext
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
text
timestamp
tinyint
uniqueidentifier
varbinary
varchar

sysname

Invalid input = NULL

Precision The number of digits of the numeric base data type:

datetime = 23
smalldatetime = 16
float = 53
real = 24
decimal (p,s) and numeric (p,s) = p
money = 19
smallmoney = 10
int = 10
smallint = 5
tinyint = 3
bit = 1
all other types = 0

int

Invalid input = NULL

Scale The number of digits to the right of the decimal point of the numeric base data type:

decimal (p,s) and numeric (p,s) = s
money and smallmoney = 4
datetime = 3
all other types = 0

int

Invalid input = NULL

TotalBytes The number of bytes required to hold both the meta data and data of the value. This information would be useful in checking the maximum side of data in a sql_variant column. If the value is greater than 900, index creation will fail. int

Invalid input = NULL

Collation Represents the collation of the particular sql_variant value. sysname

Invalid input = NULL

MaxLength The maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength of int is 4. int

Invalid input = NULL


Return Types

sql_variant

Examples

This example retrieves SQL_VARIANT_PROPERTY information on the colA value 46279.1 where colB =1689, given that tableA has colA that is of type sql_variant and colB.

CREATE   TABLE tableA(colA sql_variant, colB int)
INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)
SELECT   SQL_VARIANT_PROPERTY(colA,'BaseType'),
         SQL_VARIANT_PROPERTY(colA,'Precision'),
         SQL_VARIANT_PROPERTY(colA,'Scale')
FROM      tableA
WHERE      colB = 1689

Here is the result set. (Note that each of these three values is a sql_variant.)

decimal 8 2

See Also

sql_variant

Using sql_variant_Data