Join to table-valued function in MSSQL 2000?

Question

I have a table with a whole name. I have a function that receives said name, parses it, and returns a table with first, middle, last, and suffix. I have a bad (edit: was "hyper-conservative") DBA who won't upgrade the dev server to the same version as the production one so I can't just use APPLY and be done with it:

insert into blah (name, firstName, middleName, lastName, suffix)
select a.name, b.firstName, b.middleName, b.lastName, b.suffix
from employees a CROSS APPLY dbo.parseName(a.name) b

Please help, or I will be forced to write code like this:

insert into blah (name, firstName, middleName, lastName, suffix)
select 
    name, dbo.getNamePart(name, 'f') as firstName, 
    dbo.getNamePart(name, 'm') as middleName, 
    dbo.getNamePart(name, 'l') as lastName, 
    dbo.getNamePart(name, 's') as suffix 
from employees r

Answer 1

It sounds like "dev" and "live" use different versions? Not using the same product on your dev/production servers is a liability. A "conservative" DBA should want them to match...

I'm unclear - are you writing the function that "returns a table with first, middle, last, and suffix", or consuming it. If you are consuming it, you should just be able to SELECT from the UDF, or JOIN to it, etc. IIRC, you just alias the UDF as you would a table:

...
from dbo.myTableUdf(...) x
inner join SomeTable y on x.id = y.id

Answer 2

You would be able to join to the UDF if it wasn't processing data from the other table. That is, you can write this:

select name, b.EXPANSION
  from employees a
  join dbo.parseName('John Smith') b
    on a.CODE = b.CODE

but only because myFunc isn't referencing any fields in tb1. That's what APPLY was created for.

The way I see it, you have three options:

1. Your insert statement with one function reference per field
2. A cursor over the result set, so the function can be called one row at a time
3. Find a way to get the dev environment in sync with production, up to and including getting a better DBA.