When The Same SQL Statement...Isn't.

This is always a fun problem to track down. Part of a DTS package I worked on had this problem where it converted ‘New York’ to ‘NE’ instead of ‘NY’ using a table full of state abbreviations. Once I found out where this problem was, I’m even more mystified at what the problem really is.

Within the SQL Designer for creating Lookup queries in DTS, I created the following query:

SELECT
	RTRIM(UPPER(Abbreviation)) AS State
FROM
	States
WHERE
	(Name = ?) OR
	(Abbreviation = ?)

This yielded two results: NE amd NY. So there’s where the error occurred. Now for the shitty part.

I take the same query and run it in SQL Analyzer like so:

SELECT
	RTRIM(UPPER(Abbreviation)) AS State
FROM
	States
WHERE
	(Name = 'New York') OR
	(Abbreviation = 'New York')

and of course, it returns one result: NY.

I don’t know what in the hell paramaterized queries do in the background within DTS, but I don’t like it.

In the end, I just ended up cheating:

SELECT
	RTRIM(UPPER(Abbreviation)) AS State
FROM
	States
WHERE
	(Name = ?) OR
	(CAST(Abbreviation AS VARCHAR(50)) = ?)