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))
``````

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