Posts

Showing posts from February, 2009

SQL Server 2005 CROSS APPLY conversion

SQL Server 2005 provides a CROSS APPLY clause which in Oracle terms allows a correlated inline view or table function.

Heres some SQL Server CROSS APPLY background.
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx

There are a couple of ways to convert this based on what exactly is trying to be done.
But here are a few methods.

There are 2 main uses of CROSS APPLY
1) CROSS APPLY with a table function
2) CROSS APPLY with a subquery (inline view in Oracleze)

1) CROSS APPLY with a table function
Jeff Smith put together this neat example of using a table function with CROSS APPLY
http://www.sqlteam.com/article/returning-complex-data-from-user-defined-functions-with-cross-apply


I expanded this example to try out a few different scenarios

drop table emails
go

create table emails (ID int primary key, EmailAddress varchar(100))
go
--added 2 rows which cant be processed by EmailParse
insert into emails
select 1,'test1@gmail.com' union all
select 2,'test2@gmail.com&…