I found out about a feature in SQL Server 2005 I didn’t know about (having come from a SQL Server 2000 shop). Cross Apply! It’s for when you want to feed the value of a table row into a function and join on the results. This is different than a cross join, because you cannot pass a table column value into a function when you do a cross join.

I whipped up a simple example. Let’s say we want the states that state with a certain letter (this is a trivial example because we could do this with a simple query as well).

select *
from letters
cross apply dbo.get_StateByFirstLetter ( letters.letter )
where letters.letter between 'L' and 'M'

-- equivalent to 
select * from dbo.get_StateByFirstLetter ( 'L' )
UNION
select * from dbo.get_StateByFirstLetter ( 'M' )

-- equivalent to (which makes the above trivial)
select * from [states] 
where left(state_name, 1) between 'L' and 'M'

Full script to setup everything (pardon the sloppiness)

-- setup the states table
CREATE TABLE [states] (
    state_name varchar(25),
    state_abbr char(2)
)

INSERT INTO states VALUES ('Alaska', 'AK');
INSERT INTO states VALUES ('Alabama', 'AL');
INSERT INTO states VALUES ('American Samoa', 'AS');
INSERT INTO states VALUES ('Arizona', 'AZ');
INSERT INTO states VALUES ('Arkansas', 'AR');
INSERT INTO states VALUES ('California', 'CA');
INSERT INTO states VALUES ('Colorado', 'CO');
INSERT INTO states VALUES ('Connecticut', 'CT');
INSERT INTO states VALUES ('Delaware', 'DE');
INSERT INTO states VALUES ('District of Columbia', 'DC');
INSERT INTO states VALUES ('Florida', 'FL');
INSERT INTO states VALUES ('Georgia', 'GA');
INSERT INTO states VALUES ('Guam', 'GU');
INSERT INTO states VALUES ('Hawaii', 'HI');
INSERT INTO states VALUES ('Idaho', 'ID');
INSERT INTO states VALUES ('Illinois', 'IL');
INSERT INTO states VALUES ('Indiana', 'IN');
INSERT INTO states VALUES ('Iowa', 'IA');
INSERT INTO states VALUES ('Kansas', 'KS');
INSERT INTO states VALUES ('Kentucky', 'KY');
INSERT INTO states VALUES ('Louisiana', 'LA');
INSERT INTO states VALUES ('Maine', 'ME');
INSERT INTO states VALUES ('Marshall Islands', 'MH');
INSERT INTO states VALUES ('Maryland', 'MD');
INSERT INTO states VALUES ('Massachusetts', 'MA');
INSERT INTO states VALUES ('Michigan', 'MI');
INSERT INTO states VALUES ('Minnesota', 'MN');
INSERT INTO states VALUES ('Mississippi', 'MS');
INSERT INTO states VALUES ('Missouri', 'MO');
INSERT INTO states VALUES ('Montana', 'MT');
INSERT INTO states VALUES ('Nebraska', 'NE');
INSERT INTO states VALUES ('Nevada', 'NV');
INSERT INTO states VALUES ('New Hampshire', 'NH');
INSERT INTO states VALUES ('New Jersey', 'NJ');
INSERT INTO states VALUES ('New Mexico', 'NM');
INSERT INTO states VALUES ('New York', 'NY');
INSERT INTO states VALUES ('North Carolina', 'NC');
INSERT INTO states VALUES ('North Dakota', 'ND');
INSERT INTO states VALUES ('Ohio', 'OH');
INSERT INTO states VALUES ('Oklahoma', 'OK');
INSERT INTO states VALUES ('Oregon', 'OR');
INSERT INTO states VALUES ('Palau', 'PW');
INSERT INTO states VALUES ('Pennsylvania', 'PA');
INSERT INTO states VALUES ('Puerto Rico', 'PR');
INSERT INTO states VALUES ('Rhode Island', 'RI');
INSERT INTO states VALUES ('South Carolina', 'SC');
INSERT INTO states VALUES ('South Dakota', 'SD');
INSERT INTO states VALUES ('Tennessee', 'TN');
INSERT INTO states VALUES ('Texas', 'TX');
INSERT INTO states VALUES ('Utah', 'UT');
INSERT INTO states VALUES ('Vermont', 'VT');
INSERT INTO states VALUES ('Virgin Islands', 'VI');
INSERT INTO states VALUES ('Virginia', 'VA');
INSERT INTO states VALUES ('Washington', 'WA');
INSERT INTO states VALUES ('West Virginia', 'WV');
INSERT INTO states VALUES ('Wisconsin', 'WI');
INSERT INTO states VALUES ('Wyoming', 'WY');
GO

-- create the function which we are applying
create function dbo.get_StateByFirstLetter(@firstLetter char(1))
RETURNS @rtn TABLE (
    state_name varchar(25),
    state_abbr char(2)
)
AS
bEGIN
    insert into @rtn
    select state_name, state_abbr from states where left(state_name,1) = @firstLetter
    return
END
GO

-- generate letters table
create table letters ( letter char(1) PRIMARY KEY )
with gen_letters as 
(
    select 65 as ascii_val
    UNION ALL
    SELECT ascii_val + 1 from gen_letters where ascii_val + 1 <= 90
)
insert into letters 
select char(ascii_val) from gen_letters

-- get results by cross applying
select *
from letters
cross apply dbo.get_StateByFirstLetter ( letters.letter )
where letters.letter between 'L' and 'M'

-- equivalent to 
select * from dbo.get_StateByFirstLetter ( 'L' )
UNION
select * from dbo.get_StateByFirstLetter ( 'M' )

And for good measure, using the WITH clause to generate a table of letters:

with gen_letters as 
(
    select 65 as ascii_val
    UNION ALL
    SELECT ascii_val + 1 from gen_letters where ascii_val + 1 <= 90
)
insert into letters select char(ascii_val) from gen_letters