Clever method to convert int to string?

I was hoping there is a neat way of converting an int value to a particular string. Somewhat akin to an enumeration but not necessarily in implementation.

SELECT 
    SOE.SafetyObsID,
    SOE.DateAdded,
    SOE.ObsType,
    SOE.DataID,
    SOE.JobID,
    Jobs.JobName
FROM    SafetyObs.SOEntry SOE
    INNER JOIN dbo.JT_Jobs Jobs
    ON  SOE.JobID = Jobs.JobId AND
        Jobs.CompanyId = @CompanyID

The field SOE.ObsType is an int 开发者_Go百科ranging from 0-2. It would be awesome if I could convert it somehow in the query. This would be helpful so I wouldn't need to convert it as an enum in my code before binding it to a gridView.

0: Employee

1: Position

2: Department

CASE SOE.ObsType
   WHEN 0 THEN '0: Employee'
   WHEN 1 THEN '1: Position'
   WHEN 2 THEN '2: Department'
END AS ObsName

Although, this is better as a lookup table. What happens when you want to have 3 = "3: Division" for example? How may pieces of code will you need to change? You aren't cluttering up the database with lookup tables, you're normalising.

If you think it's clutter, then use a prefix to signify a lookup table as opposed to a "real" data table.

If it really is static, then it could be a computed column in the SOE table so the CASE is stored once without a lookup table.

Edit, example:

It can be at the end or in the middle. Also, you should separate JOIN and WHERE (filter) conditions for clarity

SELECT 
    SOE.SafetyObsID,
    SOE.DateAdded,
    SOE.ObsType,
    CASE SOE.ObsType
       WHEN 0 THEN '0: Employee'
       WHEN 1 THEN '1: Position'
       WHEN 2 THEN '2: Department'
    END AS ObsName,
    SOE.DataID,
    SOE.JobID,
    Jobs.JobName
FROM
    SafetyObs.SOEntry SOE
    INNER JOIN
    dbo.JT_Jobs Jobs ON SOE.JobID = Jobs.JobId
WHERE
    Jobs.CompanyId = @CompanyID

or

SELECT 
    SOE.SafetyObsID,
    SOE.DateAdded,
    SOE.ObsType,
    SOE.DataID,
    SOE.JobID,
    Jobs.JobName,
    CASE SOE.ObsType
       WHEN 0 THEN '0: Employee'
       WHEN 1 THEN '1: Position'
       WHEN 2 THEN '2: Department'
    END AS ObsName
FROM
    ...
WHERE
    ...

You could always create an ObsType table with and ID column and a Text column and then just do a join in your query.

The case version is the most efficient plan but has possible code maintenance disadvantages.

You could also use a View. This compiles the mappings into the plan as a constants table. A disadvantage would be that updating the View is less easy to do through an admin front end.

USE tempdb

go

CREATE VIEW ObsTypeMapping 
AS
SELECT 0 ObsType,  'Employee' Mapping UNION ALL
SELECT 1, 'Position' UNION ALL
SELECT 2, 'Department'

go

SET STATISTICS IO ON;

SELECT 
     CASE number WHEN 0 THEN 'Employee' WHEN 1 THEN 'Position'  WHEN 2 THEN 'Department' END FROM master..spt_values v
WHERE number BETWEEN 0 AND 2
/*
Cost relative to batch 30%
Table 'spt_values'. Scan count 1, logical reads 2*/


SELECT Mapping FROM master..spt_values v
JOIN ObsTypeMapping o
ON o.ObsType = v.number
/*
Cost relative to batch 35%
Table 'spt_values'. Scan count 3, logical reads 6*/

SELECT (SELECT Mapping FROM ObsTypeMapping o WHERE o.ObsType = v.number) Mapping FROM master..spt_values v
WHERE number BETWEEN 0 AND 2
/*
Cost relative to batch 35%
Table 'spt_values'. Scan count 1, logical reads 2/*