SQL Server Alias Naming Breaks Convert Function

The vendor installs the application which includes creating the database and schema (SQL Server 2008 R2). The schema defined a certain column as varchar(128) that the application code expects to return an integer. If one of the entries for a client is a non-integer value, then HTTP 500 Internal Server Error failures prevent users from being able to login to the web site. The fix is relatively easy: update the problem value to an integer that is not already in use. This SQL returns “9999″ even though the highest value is “12467″ because the rules for sorting varchar are different than the int data type.

SELECT TOP 1 IdNum FROM table1 ORDER BY IdNum desc

No biggie, just convert it to an int.

SELECT TOP 1 CONVERT(int, IdNum) as IdNum FROM table1 ORDER BY IdNum desc

This works fine. The strangeness comes into play when the aliasing “as IdNum” uses anything other than the actual name of the column. I originally tried to rename it to compare to the first query so in my output I could compare both. If the alias is different from the column name, then the CONVERT gives in the varchar sorting result not the int as specified. These all fail.

SELECT TOP 1 CONVERT(int, IdNum) as IdNumConverted FROM table1 ORDER BY IdNum desc

SELECT TOP 1 CONVERT(int, IdNum) “IdNum2″ FROM table1 ORDER BY IdNum desc

The CAST function behaves the same way. Something about the alias name appears able to break these conversions.

Removing “TOP 1″ does not fix the behavior.

from Rants, Raves, and Rhetoric v4Rants, Raves, and Rhetoric v4 »

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s