Have you ever wondered how to identify the datatype and other properties of a variable in SQL Server? In this article, we will explore a useful function called SQL_VARIANT_PROPERTY that can help us achieve this.

Let’s consider a scenario where we have a variable, but we are unsure of its datatype. We want to determine the datatype, as well as other properties such as precision, scale, max length, and collation. With the help of T-SQL, we can easily accomplish this task.

Let’s take a look at an example:

-- Example 1: Variable is an integer
DECLARE @myVar INT
SET @myVar = 0

SELECT 
    SQL_VARIANT_PROPERTY(@myVar, 'BaseType') AS BaseType,
    SQL_VARIANT_PROPERTY(@myVar, 'Precision') AS Precision,
    SQL_VARIANT_PROPERTY(@myVar, 'Scale') AS Scale,
    SQL_VARIANT_PROPERTY(@myVar, 'TotalBytes') AS TotalBytes,
    SQL_VARIANT_PROPERTY(@myVar, 'Collation') AS Collation,
    SQL_VARIANT_PROPERTY(@myVar, 'MaxLength') AS MaxLength

In this example, we declare a variable @myVar as an integer and assign it a value of 0. We then use the SQL_VARIANT_PROPERTY function to retrieve the datatype and other properties of the variable. The function returns the BaseType as “int”, Precision as “10”, Scale as “0”, TotalBytes as “4”, Collation as “NULL”, and MaxLength as “NULL”.

-- Example 2: Variable is a varchar(100)
DECLARE @myVar VARCHAR(100)
SET @myVar = 'Hello, World!'

SELECT 
    SQL_VARIANT_PROPERTY(@myVar, 'BaseType') AS BaseType,
    SQL_VARIANT_PROPERTY(@myVar, 'Precision') AS Precision,
    SQL_VARIANT_PROPERTY(@myVar, 'Scale') AS Scale,
    SQL_VARIANT_PROPERTY(@myVar, 'TotalBytes') AS TotalBytes,
    SQL_VARIANT_PROPERTY(@myVar, 'Collation') AS Collation,
    SQL_VARIANT_PROPERTY(@myVar, 'MaxLength') AS MaxLength

In this second example, we declare a variable @myVar as a varchar(100) and assign it the value ‘Hello, World!’. Again, we use the SQL_VARIANT_PROPERTY function to retrieve the datatype and other properties of the variable. The function returns the BaseType as “varchar”, Precision as “0”, Scale as “0”, TotalBytes as “202”, Collation as “NULL”, and MaxLength as “100”.

The SQL_VARIANT_PROPERTY function is a powerful tool that allows us to dynamically determine the datatype and properties of a variable in SQL Server. It can be particularly useful in scenarios where we need to handle variables with unknown datatypes or when we want to perform specific actions based on the properties of a variable.

Have you ever used the SQL_VARIANT_PROPERTY function in your production environment? Let us know your thoughts and experiences in the comments below!