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,