Novick Software
SQL Server Consulting Design Programming Tuning

  andy novick is a sql server mvp



SQL Server T-SQL User-Defined Function of the Week

What's the Data Type of that sql_variant?

Volume 1 Number 12   February 4, 2003

Sign up for this newsletter at:
SQL Server 2000 introduced the sql_variant data type.  It can 
hold any of the basic data types.  It can't hold text, ntext,
image, or timestamp data types. sql_variant can be used for the 
type of a column, parameter, variable or as the return value of 
a UDF.

Our UDF of the week turns a sql_variant value into the name of
the data type of the value.  It's based on the build-in function 
SQL_VARIANT_PROPERTY, which gives you properties of the 
sql_variant one at a time.  Our UDF calls it four times to get
the information it needs for all data types.  Here's the 

/------------- Copy From Below this line ----------------------\
CREATE FUNCTION udf_SQL_VariantToDatatypeName (

    @InVal sql_variant -- input variant

) RETURNS sysname -- The name of the data type. i. e. int
* Returns the data type name of a sql_variant
* Common Usage:
select dbo.udf_SQL_VariantToDatatypeName ('1976-07-04') --varchar
     , dbo.udf_SQL_VariantToDatatypeName (3.743) -- numeric (4,3)
* Test:
PRINT 'Test 1          ' + CASE WHEN N'nvarchar(5)' = 
     dbo.udf_SQL_VariantToDatatypeName(N'abcde' ) 
                THEN 'WORKS' ELSE 'ERROR' END
PRINT 'Test 2          ' + CASE WHEN N'datetime' = 
                         (convert(datetime, '1976-7-4', 121)) 
                           THEN 'WORKS' ELSE 'ERROR' END
*  Copyright 2003 Andrew Novick
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of the Week Vol 1, #12

DECLARE @OutStr    sysname  -- The result
      , @BaseType  sysname  -- base data type property
      , @Precision int      -- digits of the numeric base types
      , @Scale     int      -- digits to the right of the decimal
      , @MaxLen    int      -- Maximum data type length

-- Get the properties of the variant that we'll need.
                                 (@InVal, 'BaseType') as sysname)
     , @Precision = CAST(SQL_VARIANT_PROPERTY 
                                 (@InVal, 'Precision') as int)
     , @Scale     = CAST(SQL_VARIANT_PROPERTY 
                                 (@InVal, 'Scale') as int)
                                 (@InVal, 'MaxLength') as int)

    -- Adjust the length for Unicode strings
    IF lower(@BaseType) in ('nchar', 'nvarchar')
         SET @MaxLen = @MaxLen / 2

    -- construct the type name
    SET @OutStr = ltrim(rtrim(@BaseType))
           + Case WHEN @BaseType in ('char', 'varchar'
                                       , 'nvarchar', 'nchar')
                  THEN '(' + CONVERT (varchar(4), @MaxLen) + ')'
                  WHEN @BaseType in ('numeric', 'decimal')
                  THEN '(' + Convert(varchar(2), @Precision) 
                       + ', ' + convert(varchar(2), @Scale) + ')'
             ELSE '' -- empty string

    RETURN @OutStr

GRANT  EXECUTE  ON [dbo].[udf_SQL_VariantToDatatypeName]  
                                         TO [PUBLIC]
\------------ Stop copying above this line --------------------/

You won't find to many sql_variants in most databases.  To find 
them run this query that searches the syscolumns table for 
columns with type sql_variant:

/------------- Copy From Below this line ----------------------\
use pubs

SELECT OBJECT_NAME(id) as [Table], [name], [length] 
    FROM syscolumns
    WHERE xtype = (SELECT xtype 
                      FROM systypes 
                      WHERE [name] = 'sql_variant')
\------------ Stop copying above this line --------------------/
Table                       name                         length 
--------------------------- ---------------------------- ------ 
sysproperties               value                          8016 
(End of results)

The value column in the sysproperties table is the only
sql_variant that you'll find, unless you've used it in your own
user tables.

sysproperties holds the values for extended properties that you
can use to manage and document your databases.  A couple of the 
SQL Server client tools, Enterprise Manager and Query Analyzer
can be used to enter extended properties.  Enterprise Manager 
stores descriptions attached to tables and columns in extended
properties.  Query Analyzer has the Extended Properties... menu
item in the context menu of the Object Browser.

You can also add or update extended properties with the system 
stored procedures sp_addextendedproperty and 
sp_updateextendedproperty.  Let's add a few extended properties 
to the authors table in the pubs database:

/------------- Copy From Below this line ----------------------\
use pubs

exec sp_addextendedproperty 'Responsible Programmer' , 'Andrew'
                     , 'user', 'dbo' -- id of the object owner
                     , 'table', 'authors' -- which table

DECLARE @DT datetime
SET @DT = getdate()
exec sp_addextendedproperty 'Assignment Date', @DT
                     , 'user', 'dbo' -- id of the object owner
                     , 'table', 'authors' -- which table
exec sp_addextendedproperty 'Version', 1
                     , 'user', 'dbo' -- id of the object owner
                     , 'table', 'authors' -- which table
\------------ Stop copying above this line --------------------/
Instead of reading extended properties directly from the
sysproperties table, the proper way to access them is by 
using the system function fn_listextendedproperty.  Here's a
query that retrieves the extended properties for the Authors
table that we just added.  The [Type] column uses our UDF of the
week to display the data type for each extended property.

/------------- Copy From Below this line ----------------------\
use pubs
SELECT [name]
       , value
       , dbo.udf_SQL_VariantToDatatypeName(value) as [Type]
    FROM ::fn_listextendedproperty(NULL 
                           , 'USER', 'dbo'
                           , 'TABLE', 'authors'
                           , NULL, NULL)
\------------ Stop copying above this line --------------------/
name                   value                   Type   
---------------------- ----------------------- ------------
Assignment Date        2003-01-26 03:03:56.793 datetime  
Responsible Programmer Andrew                  varchar(6)
Version                1                       int 
(End of results)

I use udf_SQL_VariantToDatatypeName in a report that summarizes 
my use of extended properties for a database.  You might find it
useful whenever you use sql_variant to store data.

Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to: 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Andrew Novick


Personal Blog

New Tips:

Use dsinit to set the SQL Server instance for Windows Azure dev storage


Nov 7, '12
Loser: DB

Full Schedule