Sometimes when writing T-SQL code (or code in any other language), it can be useful to know the data type of a value returned from an object. I was working on a tutorial recently where I wanted to show you the data type that was returned by an operation, but I didn’t know how to do it.
I got on the ol’ Google machine and did a bit of digging. Eventually, I found the answer:
Use the SQL_VARIANT_PROPERTY system function
As an example, maybe we want to know the data type of the value returned by the GETDATE() system function.
We can use the SQL_VARIANT_PROPERTY function to get it. It takes two parameters:
- The expression for which you want the data type of the resulting value.
- The Property Name, which in this case will simply be the word ‘BaseType’.
I suppose this system function is a Scalar function, which means we can simply call it in a SELECT list:
That was easy!
What about the data type of a value returned by one of our own user defined functions?
Take a look at these two tables, Products and Orders:
I have a handy function called getProdSum that returns how much fresh cash we have made from a specific product whose ID we pass to the function. For example, here is how much money we have made from our Coffee Table product (which is Product # 24):
If we want to know the data type of this value, we can basically plug in that same call to our SQL_VARIANT_PROPERTY function:
Very cool stuff.
These are very simple examples, but I hope they help you in your quest to understand the T-SQL language!
Next Steps:
Leave a comment if you found this tutorial helpful! If there is a different/better way to derive the data type of a return value, I’d love to hear it. Leave a comment.
Need to know the most common data types in SQL Server? I got you:
FREE EBook on SQL Server Data Types!
Do you know the answer to these other SQL questions?
-
How do I change a column name in SQL Server?
-
Escape a single quote in SQL Server: How is it done?
-
SQL Server NULL: Are you making these 7 mistakes?
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
