in Programming |

Sql Cross Apply

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).

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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)

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

?
1
2
3
4
5
6
7
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

Comments are closed.