Good after noon

I've succesfully used a scaler valued function in a update query
ie

UPDATE ProductItemDH
SET "ItemId"=get_itemid(ProductItemDH.field1)

But now I need to set serveral fields ie


UPDATE ProductItemDH
SET "ItemId"=get_itemid(ProductItemDH.field1)
SET "desciption"=get_description(ProductItemDH.field1)
SET "value"=get_value(ProductItemDH.field1)

BUT what I really want is a table valued function that returns all three
values
so the update would look some thing like this

UPDATE ProductItemDH
SET ("ItemId",desciption", "value")
=select(get_table_values(ProductItemDH.field1))

where get_table_values is a table value function.

Anyone got an example how to do this?


thanks


David Hills

You want to use CROSS APPLY (which requires that you are on SQL 2005 or
later), for example,

Create Table dbo.FooTable (field1 int, ItemID int, ItemDescription
varchar(20), ItemValue decimal(9,2));
go

Create Function dbo.FooFunction (@field int)
Returns Table As
Return (Select @field * 10 As ItemIDResult,
'Item Number - ' + Cast(@field As varchar(10)) As ItemDescriptionResult,
@field / 100. As ItemValueResult);
go

Insert dbo.FooTable (field1)
Select 1
Union All Select 256
Union All Select 1475;

Select 'Before', field1, ItemID, ItemDescription, ItemValue
From dbo.FooTable;

Update ft
Set ItemID = f.ItemIDResult,
ItemDescription = f.ItemDescriptionResult,
ItemValue = f.ItemValueResult
From dbo.FooTable ft
Cross Apply dbo.FooFunction(ft.field1) f;

Select 'After', field1, ItemID, ItemDescription, ItemValue
From dbo.FooTable;

Drop Function dbo.FooFunction;
go

Drop Table dbo.FooTable;
go

Tom
"David Hills" <David (AT) discussions (DOT) microsoft.com> wrote

works great
--
Thanks



David Hills


"Tom Cooper" wrote: