| |
 |
|
SQL Server Tips by Burleson |
Variant data type
The variant data type can store data from many different data types
and a reference indicating which data type it is. This is very
useful for an input parameter from an SP that can handle many
different data types or a column in a table with heterogeneous data.
The following example shows how to work with it:
--declare variables
DECLARE @v sql_variant, @i int, @c char(4), @t varchar(4)
--set variables’ values for test
SET @i=13456
SET @c='abc'
SET @t='abc'
--assign integer to variant and get properties
SET @v=@i
SELECT SQL_VARIANT_PROPERTY(@v,'BaseType'), SQL_VARIANT_PROPERTY(@v,'TotalBytes'),
SQL_VARIANT_PROPERTY(@v,'MaxLength')
--assign char to variant and get properties
SET @v=@c
SELECT SQL_VARIANT_PROPERTY(@v,'BaseType'), SQL_VARIANT_PROPERTY(@v,'TotalBytes'),
SQL_VARIANT_PROPERTY(@v,'MaxLength')
--assign varchar to variant and get properties
SET @v=@t
SELECT SQL_VARIANT_PROPERTY(@v,'BaseType'), SQL_VARIANT_PROPERTY(@v,'TotalBytes'),
SQL_VARIANT_PROPERTY(@v,'MaxLength')
CREATE TABLE #tmp(Vcolumn SQL_VARIANT)
INSERT #tmp VALUES(@i)
INSERT #tmp VALUES(@c)
INSERT #tmp VALUES(@t)
SELECT Vcolumn, SQL_VARIANT_PROPERTY(Vcolumn,'BaseType') FROM #tmp
DROP TABLE #tmp
The variant data type keeps track of the original data type, current
length and maximum length, available through these properties.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
