Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Identify datatype of variable/column value using SQL_VARIANT_PROPERTY function

Dec 20 2013 12:00AM by Madhivanan   


Pinal Dave, Blogger,Speaker and an author, posted about SQL SERVER – How to Identify Datatypes and Properties of Variable where he showed a method of finding out datatype and other informations of a user defined variable. 

This function is not only useful to findout datatype of variable, but you can effectively use it to find the datatype of the column values where datatype is SQL_Variant. Consider the following table where all three columns are of SQL_Variant datatype and can have any data (mix up of various datatypes)
create table sql_variant_test(col1 sql_variant, col2 sql_variant, col3 sql_variant)
GO

insert into sql_variant_test(col1,col2,col3)
select 1,100.23,getdate() 

insert into sql_variant_test(col1,col2,col3)
select 2,'test',2000.80

select * from sql_variant_test 
The result is
col1    col2        col3
------- ----------  ------------------------
1       100.23      2013-12-19 11:27:51.030
2       test        2000.80
Now, if you want to find out datatype of each of the column values, you can use the following query
SELECT 
	SQL_VARIANT_PROPERTY(col1,'BaseType') col1_datatype,
	SQL_VARIANT_PROPERTY(col2,'BaseType') col2_datatype,
	SQL_VARIANT_PROPERTY(col3,'BaseType') col3_datatype
from sql_variant_test 

The result is
col1_datatype  col2_datatype  col3_datatype
-------------  -------------  ---------------
int            numeric        datetime
int            varchar        numeric

Tags: 


Madhivanan
2 · 40% · 13039
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Identify datatype of variable/column value using SQL_VARIANT_PROPERTY function" rated 5 out of 5 by 2 readers
Identify datatype of variable/column value using SQL_VARIANT_PROPERTY function , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]