|
|
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:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
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
CREATE FUNCTION script:
/------------- 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' =
dbo.udf_SQL_VariantToDatatypeName
(convert(datetime, '1976-7-4', 121))
THEN 'WORKS' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* 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
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
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.
SELECT @BaseType = CAST(SQL_VARIANT_PROPERTY
(@InVal, 'BaseType') as sysname)
, @Precision = CAST(SQL_VARIANT_PROPERTY
(@InVal, 'Precision') as int)
, @Scale = CAST(SQL_VARIANT_PROPERTY
(@InVal, 'Scale') as int)
, @MaxLen = CAST(SQL_VARIANT_PROPERTY
(@InVal, 'MaxLength') as int)
-- Adjust the length for Unicode strings
IF lower(@BaseType) in ('nchar', 'nvarchar')
SET @MaxLen = @MaxLen / 2
--ENDIF
-- 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
END
RETURN @OutStr
END
GRANT EXECUTE ON [dbo].[udf_SQL_VariantToDatatypeName]
TO [PUBLIC]
GO
\------------ 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
GO
SELECT OBJECT_NAME(id) as [Table], [name], [length]
FROM syscolumns
WHERE xtype = (SELECT xtype
FROM systypes
WHERE [name] = 'sql_variant')
GO
\------------ Stop copying above this line --------------------/
(Results)
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
go
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
GO
\------------ 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
go
SELECT [name]
, value
, dbo.udf_SQL_VariantToDatatypeName(value) as [Type]
FROM ::fn_listextendedproperty(NULL
, 'USER', 'dbo'
, 'TABLE', 'authors'
, NULL, NULL)
GO
\------------ Stop copying above this line --------------------/
(Results)
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:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
