XML Feeds
I posted a puzzle here Prove that this is an integer asking people to prove that the following code is indeed an int
- DECLARE @d INT
- SELECT @d = 500
or this the number 5 itself, how do you know that this is an integer?
Well this is pretty easy, you can sql_variant_property with the BaseType property. Run this code
- IF CAST(SQL_VARIANT_PROPERTY(@d,'BaseType') AS VARCHAR(20)) = 'int'
- PRINT 'yes'
- ELSE
- PRINT 'no'
As you can see yes is printed, the code below will return int for the number 5
- SELECT CAST(SQL_VARIANT_PROPERTY(5,'BaseType') AS VARCHAR(20))
what about scale and precision?
Here run this
- SELECT
- CAST(SQL_VARIANT_PROPERTY(14400195639.123,'BaseType') AS VARCHAR(20)) + '(' +
- CAST(SQL_VARIANT_PROPERTY(14400195639.123,'Precision') AS VARCHAR(10)) + ',' +
- CAST(SQL_VARIANT_PROPERTY(14400195639.123,'Scale') AS VARCHAR(10)) + ')'
Running that code will return numeric(14,3)
Now, why would you ever need this? Sometimes it is handy whene you have a table like this
- DECLARE TABLE Foo(bar SMALLINT ,col1 VARCHAR(40), col2..........)
when you run a query like this
- SELECT * FROM Foo WHERE bar = 3
you might get an index scan because 3 is not a smallint and you get a conversion. Here is a way to get around that
- SELECT * FROM Foo WHERE bar = CONVERT(SMALLINT,3)
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.