I posted a puzzle here Prove that this is an integer asking people to prove that the following code is indeed an int

 T-SQL ```1 2 ``` ```DECLARE @d INT SELECT @d = 500```
```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

 T-SQL ```1 2 3 4 ``` ```IF cast(sql_variant_property(@d,'BaseType') as varchar(20)) = 'int' PRINT 'yes' ELSE PRINT 'no'```
```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

 T-SQL ```1 ``` `select cast(sql_variant_property(5,'BaseType') as varchar(20))`
`select cast(sql_variant_property(5,'BaseType') as varchar(20))`

Here run this

 T-SQL ```1 2 3 4 ``` ```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)) + ')' ```
```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

 T-SQL ```1 ``` `declare table Foo(bar smallint ,col1 varchar(40), col2..........)`
`declare table Foo(bar smallint ,col1 varchar(40), col2..........)`

when you run a query like this

 T-SQL ```1 ``` `select * from Foo where bar = 3`
`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

 T-SQL ```1 ``` `select * from Foo where bar = convert(smallint,3)`
`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