How pass column to udf in join

Group,

Passing inline values to a udf is straightforward. However, how or is it
possible to pass a column from the select of one table into a udf that
returns a table variable in a join to the original table. The goal is to
explode the number of rows in the result set out based on the result of the
udf. Although the example I am providing here is simplified, we are trying
to parse out multiple values out of a text column and using a select into so
the result will be one row for each of row in the original table compounded
by the number of occurrences in the text field.
(I know bad table design but that's out of my control)

Thanks,
Ray

create table TableOne
(
Col1 int,
Col2 char(5)
)
go

insert TableOne values (1, 'One')
insert TableOne values (2, 'Two')
insert TableOne values (3, 'Three')
go

Create Function dbo.udfTableTwo
(@.Id int)
RETURNS @.tbl TABLE (TID int, TChar char(1))
AS
BEGIN
Declare @.test int
set @.test = @.Id
While @.test >= 0
Begin
INSERT @.tbl VALUES (@.Id, Char(65+@.test))
set @.test = @.test - 1
End
RETURN
END

--works
select a.*, b.* from TableOne a join dbo.udfTableTwo(2) b
on a.col1=b.TID

--Fails with Line 1: Incorrect syntax near '.'.
select a.*, b.* from TableOne a join dbo.udfTableTwo(a.col1) b
on a.col1=b.TID

drop table TableOne
go
drop function dbo.udfTableTwo
goSQL Server Books Online states:
Parameters can take the place only of constants; they cannot be used in
place of table names, column names, or the names of other database objects.

This seems strikingly applicable for inline table valued UDFs.

--
Anith|||Anith,

It seems to me that this statement from BOL is in regards to parameters as
they are used inside the function. In the example, I am not doing such.
Perhaps I'm clinging to hope but it isn't sounding like the answer has been
found. When dealing with hundreds of millions of rows in an ETL process, I
really like to avoid reading a table twice or using cursors.

All insights are appreciated.
Ray

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:LfcQd.383$kU3.68@.newsread1.news.pas.earthlink .net...
> SQL Server Books Online states:
> Parameters can take the place only of constants; they cannot be used in
> place of table names, column names, or the names of other database
> objects.
> This seems strikingly applicable for inline table valued UDFs.
> --
> Anith|||On Tue, 15 Feb 2005 00:39:12 GMT, Ray wrote:

>Passing inline values to a udf is straightforward. However, how or is it
>possible to pass a column from the select of one table into a udf that
>returns a table variable in a join to the original table.

Hi Ray,

This is not possible. Your code is in fact not trying to join TableOne to
one other table, but to a table of tables (since dbo.udfTableTwo would
have a different content for each row in TableOne).

To me, it makes sense that this won't work. It seems logical that the
table has to be materialized before it can be joined to another table, but
you can't materialize the udfTableTwo table without knowing the values of
TableOne.col1.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You can't reference a column from another table as a parameter to a
table-valued UDF. This makes perfect sense when you consider that, in
theory, the server has to be able to materialize the derived tables
after the FROM clause before performing the join. The same principle
also prevents you referencing columns from other tables in a derived
table query. The syntax is extended in SQL Server 2005 with the CROSS
APPLY operator to allow this type of correlated query but in 2000 it
isn't supported.

A multi-statement TVF seems unlikely to be the most efficient solution
for you. How about just creating TableTwo and using it in a join:

CREATE TableTwo (tid INTEGER PRIMARY KEY, tchar CHAR(1) NOT NULL
UNIQUE)

SELECT A.*, B.*
FROM TableOne AS A,
TableTwo AS B
WHERE A.col1 <= B.tid

--
David Portas
SQL Server MVP
--|||CORRECTION. That should be:

SELECT A.*, B.*
FROM TableOne AS A,
TableTwo AS B
WHERE A.col1 >= B.tid

--
David Portas
SQL Server MVP
--|||Ray (someone@.nowhere.com) writes:
> --Fails with Line 1: Incorrect syntax near '.'.
> select a.*, b.* from TableOne a join dbo.udfTableTwo(a.col1) b
> on a.col1=b.TID

As Aniht, Hugo and David have pointed out, there is no way you can do
this in SQL 2000.

However, in SQL 2005, currently in beta, you can do this:

select a.*, b.* from TableOne a cross apply dbo.udfTableTwo(a.Col1) b

I have not really dug into the details of the APPLY operator, so
I cannot say whether this would give you expected output in your case,
but this is what I get from your sample:

Col1 Col2 TID TChar
---- -- ---- --
1 One 1 B
1 One 1 A
2 Two 2 C
2 Two 2 B
2 Two 2 A
3 Three 3 D
3 Three 3 C
3 Three 3 B
3 Three 3 A

(9 row(s) affected)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql