Problem with scalar functions used this way - where you pass a column as a parameter - is that they are not inlined by the query compiler. This means they are executed for each row and this is the main performance issue.

There is a trick you can do - instead of scalar function you can write a table valued function that returns 1 row. Query optimizer is then wise enough to choose different execution plan which allows to avoid the performance hit.

So the function would look like this:

CREATE FUNCTION [dbo].[fn_StaffTypeMap]

(

@EmplID CHAR(5)

)

RETURNS TABLE

AS

RETURN SELECT convert(varchar(20), [your CASE statement]) as res;

GO

HTH

Piotr