... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)


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)

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:


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

Blog Archive