>Yes, but there is an overhead in creating a temp table because it has to physically exist in tempdb whereas in this case there are unlikely to be many rows in the lookup table variable so it should only exist in memory.

In this case I would agree with you but in general only tests can tell which approch is more efficient. Take a look at INF: Frequently Asked Questions - SQL Server 2000 - Table Variables mskb #305977.

>I have had bad experiences with table variables in the past. It's a pity the function in SQL didn't work, but at least I learnt something from this.

The UDFs in SQL Server have the same problem as in VFP, they slow down queries significantly. The worst case is when UDF is called for each record in the query.