IT-Stuffs

- Microsoft introduced the sql_variant in SQL Server 2000. We can use the sql_variant to store data of an unspecified or inconsistant type Or to store data of almost any SQL Server datatype.
- A sql_variant can hold any datatype except text, ntext, image, and timestamp without conversion.
For example, the following Query will explains how this acts

Declare @var1 sql_variant, @var2 sql_variant
Declare @var3 sql_variant, @var4 sql_variant,@var5 sql_variant
Declare @IntVar as integer, @DatetimeVar as Datetime
Declare @MoneyVar as Money, @BitVar as bit,@decimalVar as float
--To Assign Values to the correct data type variables
Set @IntVar = 37
Set @DatetimeVar = '2/18/02'
Set @MoneyVar = $37.23
Set @BitVar = 1
Set @decimalVar =0.5678
--To assign the values to the Sql_variant Data type variables 
Select  @var1 = @MyInt,
 @var2 =@DatetimeVar,
 @var3 = @MoneyVar,
 @var4 = @BitVar,
 @var5=@decimalVar
--To display the Result
Select  @var1 as [Int Data Type],
 @var2 as [Datetime Data Type],
 @var3 as [Money Data Type],
 @var4 as [Bit Data Type],
 cast(@var5 as numeric(5,5)) as [Decimal Data Type]

- We can use the SQL_VARIANT_PROPERTY function to identify the Data Type.

Select sql_variant_property(@var1, 'BaseType')
SELECT sql_variant_property(@var5,'BaseType')