Guru's Guide to SQL Server Architecture and Internals, The

Lookup Queries

Although I don't recommend you use lookup queries extensively, I feel obligated to cover them and explain how they work. Essentially, a lookup query is a parameterized subquery that you can set up to be called to look up data values. (A lookup query can actually do things besides look up data values and perform other types of work for each value in the source data.) Using a lookup query is similar to opening a cursor on a table in T-SQL and calling a stored procedure or executing a subquery for each row in the cursor. You usually execute a lookup query during a transformation of some type. A lookup query can be a stored procedure call or a plain SQL query. A typical lookup query might resemble the following:

 SELECT phone FROM ...