Calling a table-valued function in an update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calling a table-valued function in an update

Author

Topic

jan.nooitgedagt
Starting Member

4 Posts

Posted - 2009-02-18 : 05:12:15

I have a table-valued function, which I can call as follows:

Update dbo.table_name
set
field1 = (select field_a from dbo.table_valued_function (param1, param2, param3, param4, param5)),
field2 = (select field_b from dbo.table_valued_function (param1, param2, param3, param4, param5)),
field3 = (select field_c from dbo.table_valued_function (param1, param2, param3, param4, param5)),
field4 = (select field_d from dbo.table_valued_function (param1, param2, param3, param4, param5))

Is there a possibility to do this without calling the function 4 times ?

The function calculates the 4 values at the same time.

Thanks in advance.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 05:22:40

Just test this once.. Don't have a sql box right now.

update a set 
		field1=field_a,
		field2=field_b,
		field3=field_c,
		field4=field_d
from
		table_name a ,(
		select field_a  ,
		field_b  ,
		field_c ,
		field_d  from dbo.table_valued_function (@param1, @param2, @param3, @param4, @param5))ss

jan.nooitgedagt
Starting Member

4 Posts

Posted - 2009-02-18 : 05:50:10

Thanks ! I try that, but it gives the message: Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

update UNI set
str_uni=str,
hnr_uni=hnr,
pkd_uni=pkd,
wpl_uni=wpl
from dbo.crs_unitest UNI,(select str,hnr,pdk,wpl from dbo.nluniform(UNI.str,UNI.hnr,UNI.tvg,UNI.pkd,UNI.wpl))

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 06:09:20

[code]update UNI set
str_uni=str,
hnr_uni=hnr,
pkd_uni=pkd,
wpl_uni=wpl
from dbo.crs_unitest UNI
CROSS APPLY
dbo.nluniform(UNI.str,UNI.hnr,UNI.tvg,UNI.pkd,UNI.wpl)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:08:10

quote:

Originally posted by sakets_2000

update UNI set 
str_uni=t.str, 
hnr_uni=t.hnr, 
pkd_uni=t.pkd, 
wpl_uni=t.wpl
from dbo.crs_unitest UNI
CROSS APPLY
dbo.nluniform(UNI.str,UNI.hnr,UNI.tvg,UNI.pkd,UNI.wpl)t

it needs as alias

jan.nooitgedagt
Starting Member

4 Posts

Posted - 2009-03-20 : 10:55:13

Thanks very much ! !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:59:28

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources