TABLE FUNCTION

13 Sep 2006 9:49 AM

SalamElias

CaN i USE A TABLE FUNCTION IN A sp
and USE THE TABLE RETURNED BY A FUNCTION INSTEAD OF THE TEMPORARY TABLE that
I declare in MY SP? IF YES? HOW I can do this PLEASE, I mean do, mainipulate
the table returned.
Thanks

13 Sep 2006 10:05 AM

ML

Could you be more specific?
Do you want to retrieve rows, change them and update the table? You could
use something like this:

update <table>
set <column assignemts>
from <table>
inner join <table function>(...) tf
on tf.<common key> = <table>.<common key>

ML

---
http://milambda.blogspot.com/

13 Sep 2006 10:18 AM

SalamElias

I have a SP that create a ##temp table that has 10 fields, 5 of them come
from a "select f1, f2, f3, f4, f5, 0, 0, 0, 0,0 from mytable". Then I update
the other fields in differnet steps.
My question is is it possible to use t afunction instead of using temp tables.

Thanks for your response

Show quoteHide quote

"ML" wrote:

> Could you be more specific?
> Do you want to retrieve rows, change them and update the table? You could
> use something like this:
>
> update <table>
>   set <column assignemts>
>   from <table>
>           inner join <table function>(...) tf
>                         on tf.<common key> = <table>.<common key>
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

13 Sep 2006 10:37 AM

Uri Dimant

Hi
See if this helps

Use pubs

CREATE TABLE #Test (id VARCHAR (50),lname VARCHAR(50))

INSERT INTO #Test VALUES ('172-32-1176','Green')

UPDATE #Test SET lname =(SELECT au_lname FROM dbo.ufn_TableValued
('172-32-1176'))
WHERE EXISTS (SELECT * FROM dbo.ufn_TableValued ('172-32-1176'))

SELECT * FROM #Test

Show quoteHide quote

"SalamElias" <eliassal@online.nospam> wrote in message
news:88C4CE35-E960-48F6-AE0E-236D03F0649B@microsoft.com...
>I have a SP that create a ##temp table that has 10 fields, 5 of them come
> from a "select f1, f2, f3, f4, f5, 0, 0, 0, 0,0 from mytable". Then I
> update
> the other fields in differnet steps.
> My question is is it possible to use t afunction instead of using temp
> tables.
>
> Thanks for your response
>
> "ML" wrote:
>
>> Could you be more specific?
>> Do you want to retrieve rows, change them and update the table? You could
>> use something like this:
>>
>> update <table>
>>   set <column assignemts>
>>   from <table>
>>           inner join <table function>(...) tf
>>                         on tf.<common key> = <table>.<common key>
>>
>>
>> ML
>>
>> ---
>> http://milambda.blogspot.com/

13 Sep 2006 10:39 AM

ML

Why are you using a global temporary table? Have you considered using a local
one or a table variable?

Generally, you can use a table-valued function for data modifications as I
suggested in my previous post, and table-valued functions can be updatable.

Could you give an example of how you intend to use a TVF for data
modifications?

ML

---
http://milambda.blogspot.com/