Checking for Valid Integers in SQL Server 2008 R2

Today I was moving code from my SQL Server 2014 dev server to a SQL Server 2008 R2 production server and I ran into a problem with a function I created a few weeks back. I tend to work across versions frequently, and up until now I have been pretty good at avoiding issues with backwards compatibility... but this one slipped by me!

The function is used during an import process and is intended to convert a term and year (think "Fall 2014") to a numeric representation of the term. The data source is a relatively dirty Excel file with lots of people entering data. While they are pretty good at being consistent, humans make mistakes with data entry. I know, that might be a surprise, but it's true. The code below is a small chunk of the error checking inside the function to catch invalid values so I can send them data integrity reports and get them to clean it up.

Original Version

The original version used TRY_CONVERT to handle checking if the year could be converted to an integer. The variable @termYear is one of the inputs to the function and is defined as VARCHAR(40) to handle the dirty source data.

IF (TRY_CONVERT(INT, @termYear) IS NULL)
    BEGIN 
        SET @term = 'ERR'
        RETURN @term
    END

This works great on SQL Server 2012 or later, but I need this on SQL Server 2008 R2. Bummer.

First Attempt - 2008 R2

My first instinct was to test out ISNUMERIC() instead of TRY_CONVERT() even though there are known issues with ISNUMERIC. If you aren't familiar with those issues, keep reading and you'll see one example of what happens. The value in @termYear that was giving me a headache was: "2016," (notice the comma!) and you can see in the screenshot that ISNUMERIC('2016,') returns true. Not false!

Then, because this returns a false positive, code later on crashes and burns trying to convert use the value. Bummer again.

To me, ISNUMERIC implies the comma would make the function return false, but it doesn't. I don't know the actual reason for this but I suspect it is related to supporting numbers using commas (,) instead of periods (.). (That's just a suspicion...)

TLDR; The Solution

I found the solution over on Stack Overflow and there is an explanation of how this works over here. With the addition of '.0e0' the ISNUMERIC function returns false as I need it to for our use case. That block of code now looks like this.

IF (ISNUMERIC(@termYear + '.0e0') = 0)
    BEGIN 
        SET @term = 'ERR'
        RETURN @term
    END

That's all for this post. It will serve as a nice reminder to myself to do my research first, especially when I know I'm using a different version, but I found a pretty slick solution in the end anyway.