Conditionally branching in SQL based on the type of a variable

Question

I'm selecting a value out of a table that can either be an integer or a nvarchar. It's stored as nvarchar. I want to conditionally call a function that will convert this value if it is an integer (that is, if it can be converted into an integer), otherwise I want to select the nvarchar with no conversion.

This is hitting a SQL Server 2005 database.

select case
    when T.Value (is integer) then SomeConversionFunction(T.Value)
    else T.Value
end as SomeAlias

from SomeTable T

Note that it is the "(is integer)" part that I'm having trouble with. Thanks in advance.

UPDATE

Check the comment on Ian's answer. It explains the why and the what a little better. Thanks to everyone for their thoughts.

Answer 1

 select case
     when ISNUMERIC(T.Value) then T.Value 
     else SomeConversionFunction(T.Value)
 end as SomeAlias

Also, have you considered using the sql_variant data type?

Answer 2

The result set can only have one type associated with it for each column, you will get an error if the first row converts to an integer and there are strings that follow:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'word' to data type int.

try this to see:

create table  testing
(
strangevalue   nvarchar(10)
)

insert into testing values (1)
insert into testing values ('word')
select * from  testing

select
    case
        when ISNUMERIC(strangevalue)=1 THEN CONVERT(int,strangevalue)
        ELSE strangevalue
     END
FROM testing

best bet is to return two columns:

select
    case
        when ISNUMERIC(strangevalue)=1 THEN CONVERT(int,strangevalue)
        ELSE NULL
     END AS StrangvalueINT
    ,case
        when ISNUMERIC(strangevalue)=1 THEN NULL
        ELSE strangevalue
     END AS StrangvalueString
FROM testing

or your application can test for numeric and do your special processing.