SQL_VARIANT_PROPERTY (Transact-SQL)
This functions 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 one of the following values.
| Value | Description | Base type of sql_variant returned |
| BaseType | SQL Server data type, such as: bigint binary char date datetime datetime2 datetimeoffset decimal float int money nchar numeric nvarchar real smalldatetime smallint smallmoney time tinyint uniqueidentifier varbinary varchar | sysname NULL = Input is not valid. |
| Precision | 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 bigint = 19 int = 10 smallint = 5 tinyint = 3 bit = 1 All other types = 0 | int NULL = Input is not valid. |
| Scale | 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 NULL = Input is not valid. |
| TotalBytes | Number of bytes required to hold both the metadata 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 larger than 900, index creation will fail. | int NULL = Input is not valid. |
| Collation | Represents the collation of the particular sql_variant value. | sysname NULL = Input is not valid. |
| MaxLength | Maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength ofint is 4. | int NULL = Input is not valid. |
Return Types
sql_variant
Examples
The following example retrieves SQL_VARIANT_PROPERTY information about the colA value 46279.1 where colB =1689, given that tableA hascolA 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') AS 'Base Type',
SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',
SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'
FROM tableA
WHERE colB = 1689
Here is the result set. Note that each of these three values is a sql_variant.
Base Type Precision Scale
--------- --------- -----
decimal 8 2
(1 row(s) affected)
Beginner School is optimized for learning. This is useful for getting basic knowledge with easiest way. Tutorials, examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. With the help of other reference book and website, the motive to provide you easiest way to understand the coding, technology and other things.....
Conversion Conversion Emoticon Emoticon