You can use a view and do the CONVERT/CAST in the view. Alternatively
you can use derived table or CTE on SQL Server 2005:

SELECT CASE WHEN code
                 BETWEEN 1000 AND 1110
                 THEN ...
FROM (SELECT CAST(Floyd.dbo.ProductGroup.Code AS INT) AS code
                    ...
         FROM Floyd.dbo.ProductGroup) AS T;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

If you don't want to type all of those CONVERT's, you can have a shorter
query by using a derived table, e.g., something like

SELECT x.mtapg_ItemID,
  x.mtapg_ItemCode,
  x.mtapg_ProductGroup,
 CASE WHEN x.mtapg_ProductGroup                 BETWEEN 1000 AND 1110
      THEN ...
      WHEN x.mtapg_ProductGroup                BETWEEN 1150 AND 1160
         OR x.mtapg_ProductGroup                BETWEEN 1165 AND 1201
      THEN ...
      WHEN x.mtapg_ProductGroup                BETWEEN 1350 AND 1380
         OR x.mtapg_ProductGroup                BETWEEN 1700 AND 1800
         OR  x.mtapg_ProductGroup IN (1161, 1162, 1915, 1990)
      THEN ...
      END AS   mtapg_EquipmentType

FROM (SELECT     Floyd.dbo.StockItem.ItemID AS mtapg_ItemID,
 Floyd.dbo.StockItem.Code AS mtapg_ItemCode,
 CONVERT(int, Floyd.dbo.ProductGroup.Code) AS mtapg_ProductGroup
FROM         Floyd.dbo.StockItem INNER JOIN
                     Floyd.dbo.ProductGroup ON
Floyd.dbo.StockItem.ProductGroupID = Floyd.dbo.ProductGroup.ProductGroupID )
As x

Note that which form you use is mostly a matter of programming style,
readiability, etc.  There is probably no significant difference in
performance between the two methods.  But derived tables (and CTE's if you
are using SQL 2005) can save you typing (and maintaining!) the same piece of
code multiple times in the same query.  Another choice would be to have a
view that includes the converted integer.  Of course, the best choice would
have been to choose the correct datatype when the database was designed.  If
ProductGroup.Code is always an integer, it makes life much, much simpler if
the datatype in the table is int.

Tom

Terry,

Just for the record, but not as a recommendation, you can
do this without the CONVERT expressions:

CASE
 WHEN Floyd.dbo.ProductGroup.Code BETWEEN 1000 AND 1110 THEN ...
 WHEN Floyd.dbo.ProductGroup.Code BETWEEN 1111 AND 1240 THEN ...
END

When a string and a number are compared, the string is implicitly
converted to a number, and the comparison is between numbers.

That said, I don't think relying on implicit type conversions
is a very good idea. But I'm not sure it makes things much
worse, given the faulty design of the table to begin with.

From what you've said, there may not be any safeguards in
place to guarantee that [Code] does in fact represent an [int],
so I would recommend that you trap any bad strings, giving a
result of NULL for them, or giving a non-NULL value that
makes sense in the context:

CASE
 WHEN Floyd.dbo.ProductGroup.Code LIKE '%[^0-9]%' THEN NULL
 WHEN ...

Checking for characters outside the range 0-9 isn't perfect, but it's close.
A few edge cases: the empty string '' would still pass the first
CASE predicate and be treated as 0; strings like '200.00' and '+20'
would be rejected (but it would be very hard to accept only such
strings that represent integer values); and numbers too large to be
represented as whatever data type SQL Server chooses for the
numbers in the CASE predicates would cause overflow.

My recommendation is to follow the suggests of Plamen and
others, and use a derived table (or a WITH expression
if you are using SQL Server 2005 or later), or
put all the CONVERTs in (though I'd use the ANSI standard
CAST instead of CONVERT, personally).

You could also vote on this Microsoft Connect item, which
suggests a new function that would be useful in some situations
like yours:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766

Steve Kass
Drew University
http://www.stevekass.com