Dumb question... I think... 1

[darrellblackhawk]

How do you determine any variable's data type.

i.e.

declare @my_money as money
declare @my_nvarchar as nvarchar(100)
select [red]TYPE[/red](@my_money), [red]TYPE[/red](@my_nvarchar)

Where [red]TYPE(...)[/red] equals some function that returns the
data type and hopefully it's precision.

Darrell

 

[JamesLean]

You can't do this with normal variables. The only similar function would be SQL_VARIANT_PROPERTY, which returns information about sql_variant values. Look it up in BOL for more info.

--James

 

[darrellblackhawk]

Thanks James... That's exactly what I needed.

Darrell

 

[darrellblackhawk]

James:

BTW/ It does appear to work with normal variables - or am I missing something?

i.e.
[tt]
// Input...

print '// @my_money'
declare @my_money as money
set @my_money = 100.200
select
@my_money as 'CV',
SQL_VARIANT_PROPERTY(@my_money,'BaseType') as 'BT',
SQL_VARIANT_PROPERTY(@my_money,'Precision') as 'PR',
SQL_VARIANT_PROPERTY(@my_money,'Scale') as 'SC',
SQL_VARIANT_PROPERTY(@my_money,'TotalBytes') as 'TB',
SQL_VARIANT_PROPERTY(@my_money,'Collation') as 'CL',
SQL_VARIANT_PROPERTY(@my_money,'MaxLength') as 'ML'
go

print '// @my_date'
declare @my_date as datetime
set @my_date = getdate()
select
@my_date as 'CV',
SQL_VARIANT_PROPERTY(@my_date,'BaseType') as 'BT',
SQL_VARIANT_PROPERTY(@my_date,'Precision') as 'PR',
SQL_VARIANT_PROPERTY(@my_date,'Scale') as 'SC',
SQL_VARIANT_PROPERTY(@my_date,'TotalBytes') as 'TB',
SQL_VARIANT_PROPERTY(@my_date,'Collation') as 'CL',
SQL_VARIANT_PROPERTY(@my_date,'MaxLength') as 'ML'
go


print '// @my_nvarchar'
declare @my_nvarchar as nvarchar(100)
set @my_nvarchar = N'My N Var Char(100)'
select
@my_nvarchar as 'CV',
SQL_VARIANT_PROPERTY(@my_nvarchar,'BaseType') as 'BT',
SQL_VARIANT_PROPERTY(@my_nvarchar,'Precision') as 'PR',
SQL_VARIANT_PROPERTY(@my_nvarchar,'Scale') as 'SC',
SQL_VARIANT_PROPERTY(@my_nvarchar,'TotalBytes') as 'TB',
SQL_VARIANT_PROPERTY(@my_nvarchar,'Collation') as 'CL',
SQL_VARIANT_PROPERTY(@my_nvarchar,'MaxLength') as 'ML'

go

// Output...

// @my_money

CV BT PR SC TB CL ML
-------- ----- ----- ----- ----- ------ -----
100.2000 money 19 4 10 (null) 8

// @my_date

CV BT PR SC TB CL ML
---------------------- -------- ----- ----- ----- ------ -----
2005-06-13 08:54:57.69 datetime 23 3 10 (null) 8

// @my_nvarchar

CV BT PR SC TB CL ML
------------------ -------- ----- ----- ----- ---------------------------- -----
My N Var Char(100) nvarchar 0 0 44 SQL_Latin1_General_CP1_CI_AS 200

[/tt]