Just another day I received very interesting question in email from Zovis and we exchanged few emails. I am converting our conversation in the chat format to understand it easily. The question was how to read the datatype and other properties of variable values in T-SQL.
Zovis: How do I identify the datatype and other properties of the variable?
Pinal: Just check the datatype of the declared variable. Isn’t that easy?
Zovis: Yes, that is true but we have absolutely different need. We do not know what is the datatype of the variable. We need to check that with the help of code.
Pinal: Can you give me quick example?
Zovis: Sure. If our variable is numeric then we want to know the precision and scale, but if our datatype is varchar we want to know the max length and collation. We want to do all this with T-SQL. Can you help?
Pinal: Hmm… well you can do it with the help of SQL_VARIANT_PROPERTY. Let me show you example.
Let us see two examples –
In the first case our variable is integer.
DECLARE @myVar INT
SET @myVar = 0
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths
In the second case our variable is varchar (100).
DECLARE @myVar VARCHAR(100)
SET @myVar = 0
SELECT SQL_VARIANT_PROPERTY(@myVar,'BaseType') BaseType,
SQL_VARIANT_PROPERTY(@myVar,'Precision') Precisions,
SQL_VARIANT_PROPERTY(@myVar,'Scale') Scale,
SQL_VARIANT_PROPERTY(@myVar,'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(@myVar,'Collation') Collation,
SQL_VARIANT_PROPERTY(@myVar,'MaxLength') MaxLengths
What do you think of this function? Have you ever used in your production environment?
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
9 Comments. Leave new
Wow superb article sir its very simple and nice
am very glad today i learned something new
I agree. Thanks Pinal Dave!
Wow, can’t believe I didn’t know about this function. I’ve been wondering how to do this for a long time.
Hi Pinal,
I check your web site every single day and it’s awesome. Thank you for the nice work
What will happen if you variable is varchar(max)
Still useful information. Thanks Pinal
Your welcome Greg!
Doesn’t work for MAX types (binary, navarchar,…)
Great article – very useful!
When concatenating strings into an NVARCHAR variable, We found that if you don’t put the N before the string, the main string changes from NVARCHAR to VARCHAR due to the concatenation of a varchar string to an NVARCHAR string.
this code doesn’t support NVARCHAR variables.
How can I have the same type of function that supports an NVARCHAR variable?