"StaffType"	= CASE 
		                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Contractor'
		                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Contractor'
		                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Contractor'
		                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Contractor'
		                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'V' AND REG_TEMP = 'T' THEN 'Vendor'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'R' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'T' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'I' AND REG_TEMP = 'T' THEN 'Employee'
		                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Employee'
		                ELSE 'ERROR'
		       END 

, "StaffType" = (SELECT dbo.fn_StaffTypeMap(p.EMPLID))

USE [HRData]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_StaffTypeMap]    Script Date: 06/17/2009 13:00:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

AUTHOR:		        Bill King
CREATE DATE:        6/16/2009
DESCRIPTION:        Used to convert the PER_ORG/Empl_Class/Reg_Temp combos to 
                           a more user friendly format.
                                    
COMMENTS:           

                    
SAMPLE EXECUTION: SELECT dbo.fn_StaffTypeMap ('16011')                    

*/

CREATE FUNCTION [dbo].[fn_StaffTypeMap] 
(
        @EmplID CHAR(5)
)

RETURNS VARCHAR(20)
AS
BEGIN
	
    DECLARE @MappedValue AS VARCHAR(20)
    SELECT @MappedValue =  (
                            SELECT CASE 
		                                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Contractor'
		                                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Contractor'
		                                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Contractor'
		                                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Contractor'
		                                WHEN PER_ORG = 'CWR' AND EMPL_CLASS = 'V' AND REG_TEMP = 'T' THEN 'Vendor'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'R' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = '' AND REG_TEMP = 'T' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'R' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'C' AND REG_TEMP = 'T' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'G' AND REG_TEMP = 'T' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'I' AND REG_TEMP = 'T' THEN 'Employee'
		                                WHEN PER_ORG = 'EMP' AND EMPL_CLASS = 'T' AND REG_TEMP = 'T' THEN 'Employee'
		                                ELSE 'ERROR'
		                           END       
                            FROM PSPerson 
                            WHERE EmplID = @EmplID
                            
                        )
    RETURN (@MappedValue)
END

 
CREATE FUNCTION [dbo].[fn_StaffTypeMap]
(
        @EmplID CHAR(5)
)
 
RETURNS TABLE 
AS
RETURN SELECT convert(varchar(20), [your CASE statement]) as res;
GO

CREATE TABLE StaffTypeMap
( PER_ORG 
, EMPL_CLASS
, REG_TEMP 
, StaffType
, PRIMARY KEY ( PER_ORG ,  EMPL_CLASS, REG_TEMP )
)

SELECT COALESCE(StaffTypeMap.StaffType, 'UNKNOWN')
FROM X
LEFT OUTER JOIN  
		StaffTypeMap
		ON  StaffTypeMap.PER_ORG	= X.PER_ORG
		AND StaffTypeMap.EMPL_CLASS = X.EMPL_CLASS
		AND StaffTypeMap.REG_TEMP	= X.REG_TEMP

USE tempdb
GO
CREATE FUNCTION dbo.f (@p1 INT, @p2 INT) 
RETURNS INT
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS BEGIN RETURN CASE WHEN @p1 = 1 AND @p2 = 1 THEN 0 WHEN @p1 = 1 AND @p2 = 2 THEN 1 WHEN @p1 = 2 AND @p2 = 1 THEN 2 ELSE 3 END END
GO
CREATE TABLE dbo.Demo (A INT NOT NULL, B INT NOT NULL, C AS dbo.f (A, B) PERSISTED);
GO
INSERT	dbo.Demo (A, B)
SELECT	TOP (25000)
		CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1),
		CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1)
FROM	master.sys.columns c1
CROSS
JOIN	master.sys.columns c2
GO
SELECT	*
FROM	dbo.Demo;
GO
DROP TABLE dbo.Demo;
DROP FUNCTION dbo.f;