SQL Server 2000 Functions vs. Usefulness

The SQL Server 2000 development team have really gone out of their way to make table-valued functions less than useful.  Here, for example, is a function that will retrieve ancestors of a row in some kind of hierarchy table:

create function ancestors(@id int)
returns @t table(id int)
as begin
  while 1=1 begin
    select @id=(select parentId from hierarchyTable where id=@id)
  if @id is null break
    insert into @t values(@id)
  end
  return
end

But don't actually try to use this function:

select a.id, anc.id from hierarchyTable a
left join ancestors(a.id) anc on (1=1)

The alias 'a' is not visible in the argument to the function call.  In fact, it appears that the only way to pass a non-constant value to a table-valued function is within a stored procedure.

Apparently, this is fixed in SQL Server 2005 using the new CROSS APPLY or OUTER APPLY join operators.  Of course, SQL Server 2005 implements recursive queries using the WITH clause for SELECT statements, so such function shenanigans are less necessary:

WITH

  data AS (
    SELECT 'grandparent' AS id, CAST(NULL AS VARCHAR(MAX)) AS parent
    UNION SELECT 'parent 1', 'grandparent'
    UNION SELECT 'parent 2', 'grandparent'
    UNION SELECT 'child 1', 'parent 1'
    UNION SELECT 'child 2', 'parent 1'
    UNION SELECT 'child 3', 'parent 2'
    UNION SELECT 'child 4', 'parent 2'
  )

, hierarchy AS (
    SELECT 0 AS level, CAST(NULL AS VARCHAR(MAX)) AS parent, id
    FROM data
    WHERE parent IS NULL
    UNION ALL
    SELECT parent.level+1, child.parent, child.id
    FROM data AS child
    INNER JOIN hierarchy AS parent ON parent.id = child.parent
  )

SELECT * from hierarchy
ORDER BY 1, 2, 3