Using a function with other tables

[Christina]

Hi,

I am in a dilemma. The scenario is:
I have a table with a long stream (1000 bytes non-fixed length)with delimited words. I need to get the words out in a separate table and do it fast.
Ideally a cursor is the solution but having added things like User defined functions and Table data types, I was tempted to use these. The master table having about 15 million rows with upto 200 delimited words in each row on an average, the cursor would have taken weeks.

I got very close. I developed the function that will accept a stream of words and return table data that could be inserted into the second table where I wanted it to be. It worked great with a constant string(see the statement below)

select *
from FnGetWords('aba ba ba baba ba baba bab ababa&#39

The only problem is now I need to pass the stream from another table:
something like this
select *
from FnGetWords(stream), Mastertable

I know a functioncan be called in a select statement but what I need to figure out is how I can use that in the From clause or pass the stream to it without using the cursor.

Any immediate help will be highly appreciated..

Thanks tons in anticipation,
Christina

[Deb Crow]

Christina;

Do you mean SELECT * FROM dbo.FnGetWords

OR

INSERT [permanent table]
SELECT * FROM dbo.FnGetWords

You must specifiy the owner of the function in any of your statements. It can be called just like any table. You can also return a table in your function. When you create it you declare a datatype TABLE and give it a name. It can be used sort of like a temp table.

Kalen Delaney has several articles in SQL Mag since October of last year on different ways to use User Defined Functions.

I hope this helps.

Deb


------------
Christina at 2/28/01 9:10:52 PM

Hi,

I am in a dilemma. The scenario is:
I have a table with a long stream (1000 bytes non-fixed length)with delimited words. I need to get the words out in a separate table and do it fast.
Ideally a cursor is the solution but having added things like User defined functions and Table data types, I was tempted to use these. The master table having about 15 million rows with upto 200 delimited words in each row on an average, the cursor would have taken weeks.

I got very close. I developed the function that will accept a stream of words and return table data that could be inserted into the second table where I wanted it to be. It worked great with a constant string(see the statement below)

select *
from FnGetWords('aba ba ba baba ba baba bab ababa&#39

The only problem is now I need to pass the stream from another table:
something like this
select *
from FnGetWords(stream), Mastertable

I know a functioncan be called in a select statement but what I need to figure out is how I can use that in the From clause or pass the stream to it without using the cursor.

Any immediate help will be highly appreciated..

Thanks tons in anticipation,
Christina