SQL - SQL_VARIANT_PROPERTY() Function

---

---

The SQL_VARIANT_PROPERTY() function returns the base data types and other information about a sql_variant value.

sql_variant can be used in columns, parameter, variables, and the returned value of the user defined function. sql_variant enables these database objects to support values of other data types.

A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values.

A sql_variant data type must be cast to its base data type value before participating in operations such as addition and subtraction.

The maximum length of sql_variant is 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.

Syntax

Following is the syntax of the SQL_VARIANT_PROPERTY() function −

SELECT SQL_VARIANT_PROPERTY( expression , property )

The function accepts two arguments: the sql_variant value, and the property for which information is to be needed.

Example

Let's look at the example of SQL_VARIANT_PROPERTY function in SQL

To demonstrate this, we have declared a sql_variant variable, assigned a value that is type varchar, and passed this variable inside the SQL_VARIANT_PROPERTY function.

   DECLARE @var sql_variant = 'tutorialspoint!';
   SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType') AS BaseType;

Output

following is the output of the above SQL query that returns base data types −

+----------+
| BaseType |
+----------+
| varchar  |
+----------+

Example

In the following example, we are declaring a sql_variant variable of another data type and checking the data type by using the SQL_VARIANT_PROPERTY function in SQL.

Following is the query −

   DECLARE @var sql_variant = 23452345.669;
   SELECT SQL_VARIANT_PROPERTY(@var, 'BaseType') AS BaseType;

Output

following is the output of the above SQL query that returns base data types −

+----------+
| BaseType |
+----------+
| numeric  |
+----------+