Programming in MS SQL Server 2012

Variant Data Type

Variant data type is used to stores different data types that based on reference variable or column's data type. Variant data type helps to maintain different kind of input parameter's data values from a stored procedure or a different kind of reference column's data values from a table object. The following example describes a Variant Data Types with reference of Integer, Char and Varchar Data Types.

-- Example 79 --

DECLARE @DataInt SQL_Variant,

@DataChar SQL_Variant,

@DataVarChar SQL_Variant,

@Emp_Code int,

@Emp_Name char(50),

@Emp_Department varchar(50)

SET @Emp_Code = 55

SET @Emp_Name = 'Prem Nath R.K.'

SET @Emp_Department = 'E.D.P Department'

SET @DataInt = @Emp_Code

SET @DataChar = @Emp_Name

SET @DataVarChar = @Emp_Department

SELECT 'Employee Code' = @DataInt,

'Base Data Type' = SQL_VARIANT_PROPERTY(@DataInt, 'BaseType'),

'Memory Allocation in Bytes' = SQL_VARIANT_PROPERTY(@DataInt, 'TotalBytes'),

'Base Data Size' = SQL_VARIANT_PROPERTY(@DataInt, 'MaxLength')

SELECT 'Employee Name' = @DataChar,

'Base Data Type' = SQL_VARIANT_PROPERTY(@DataChar, 'BaseType'),

'Memory Allocation in Bytes' = SQL_VARIANT_PROPERTY(@DataChar, 'TotalBytes'),

'Base Data Size' = SQL_VARIANT_PROPERTY(@DataChar, 'MaxLength')

SELECT 'Employee Department' = @DataVarChar,

'Base Data Type' = SQL_VARIANT_PROPERTY(@DataVarChar, 'BaseType'),

'Memory Allocation in Bytes' = SQL_VARIANT_PROPERTY(@DataVarChar, 'TotalBytes'),

'Base Data Size' = SQL_VARIANT_PROPERTY(@DataVarChar, 'MaxLength')

Query Output Screen

* * * * *

Your Comment To AUTHOR