|
|
Posted by SQLMan_25 on 01/22/07 14:37
Hi All,
I am trying to create a user defined function that fetches the price of
an item. I have written a scalar function that takes itemid and returns
its price.
Simple version of tables would be like this:
Item(
itemid int,--pk
price decimal(18,4)
)
item_detail(
redid int , --pk
itemid int, --fk
cust_id int,--fk
order_qty decimal(18,4)
)
Now I want to create a computed persisted column having formula
dbo.GetPrice(Itemid) * order_qty
I get the error cannot be persisted because column is
non-deterministic.
function is as following:
create function GetPrice(@itemid int)
Returns decimal(18,4)
AS
Begin
declare @price decimal(18,4)
select @price =price from Item where itemid=@itemid
return @price
End
Go
I would appreciate if someone can shed some light that why this
function is considered non-deterministic by SQL Server 2005. Is there
any work around this behaviour?
Thanks in advance for all your remarks.
NH
Navigation:
[Reply to this message]
|