Non-deterministic UDF's?

Hello all, i want to make a non-deterministic UDF... is this possible?
Heres what i have... its an online catalog. The site has an option
DAYS_LIST_AS_NEW, where the owner can specify a number of
days for which a product is listed as new, so if the user puts a value of
30, products are listed on the "New Items" page for 30 days after the
value stored in their CREATEDATE field.
The product records also have a bit field LIST_AS_NEW which can
be used to override the time-calculation, and list a product as new even
after the x-day period has gone by.
I want to create a function that basically does whats below.
Thanks in advance for any help.
(code may not be perfect here.)
========================================
==============
FUNCTION getItemIsNew(@.ITEMID INT)
RETURNS BIT
BEGIN
DECLARE @.ALWAYS
SELECT @.ALWAYS= LIST_AS_NEW FROM PRODUCTS
WHERE ROWID = @.ITEMID
IF @.ALWAYS= 1
RETURN 1
DECLARE @.DAYS INT
SELECT @.DAYS = VAL FROM WEB_OPTS
WHERE OPT = 'DAYS_ON_NEW_LIST'
DECLARE @.DAYSGONE INT
SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, GETDATE())
FROM PRODUCTS WHERE ROWID = @.ITEMID
IF @.DAYSGONE > @.DAYS
RETURN 0
ELSE
RETURN 1
ENDGetdate() is not allowed in UDF. You could create a view and call it.
e.g.
create view get_date
as
select dt=getdate()
go
SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, (select dt from get_date))
FROM PRODUCTS WHERE ROWID = @.ITEMID
-oj
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:uhkK6$VxFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hello all, i want to make a non-deterministic UDF... is this possible?
> Heres what i have... its an online catalog. The site has an option
> DAYS_LIST_AS_NEW, where the owner can specify a number of
> days for which a product is listed as new, so if the user puts a value of
> 30, products are listed on the "New Items" page for 30 days after the
> value stored in their CREATEDATE field.
> The product records also have a bit field LIST_AS_NEW which can
> be used to override the time-calculation, and list a product as new even
> after the x-day period has gone by.
> I want to create a function that basically does whats below.
> Thanks in advance for any help.
> (code may not be perfect here.)
> ========================================
==============
> FUNCTION getItemIsNew(@.ITEMID INT)
> RETURNS BIT
> BEGIN
> DECLARE @.ALWAYS
> SELECT @.ALWAYS= LIST_AS_NEW FROM PRODUCTS
> WHERE ROWID = @.ITEMID
> IF @.ALWAYS= 1
> RETURN 1
> DECLARE @.DAYS INT
> SELECT @.DAYS = VAL FROM WEB_OPTS
> WHERE OPT = 'DAYS_ON_NEW_LIST'
> DECLARE @.DAYSGONE INT
> SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, GETDATE())
> FROM PRODUCTS WHERE ROWID = @.ITEMID
> IF @.DAYSGONE > @.DAYS
> RETURN 0
> ELSE
> RETURN 1
> END
>|||Yah, i saw a site with an example like that, but thought maybe
someone might have some more natural way to do this.
Anyone know is SS2K5 will support non-deterministic UDF's?
It really is a pretty cumbersome restriction.
Thanks in advance.
"oj" <nospam_ojngo@.home.com> wrote in message
news:O3bX2TWxFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Getdate() is not allowed in UDF. You could create a view and call it.
> e.g.
> create view get_date
> as
> select dt=getdate()
> go
>
> SELECT @.DAYSGONE = DATEDIFF(dd, CREATEDATE, (select dt from get_date))
> FROM PRODUCTS WHERE ROWID = @.ITEMID
>
> --
> -oj
>
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:uhkK6$VxFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Some restrictions are removed in SQL Server 2005.
It makes it a lot harder to architect and non-deterministic functions as
each call to the function may return a different value.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:uNVvk3WxFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Yah, i saw a site with an example like that, but thought maybe
> someone might have some more natural way to do this.
> Anyone know is SS2K5 will support non-deterministic UDF's?
> It really is a pretty cumbersome restriction.
> Thanks in advance.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O3bX2TWxFHA.2728@.TK2MSFTNGP14.phx.gbl...
>|||Reading the value from a view can end up having a serious impact on
performance. Consider supplying the datatime value through a parameter.
ML|||Thanks for the info,
Good to know well be able to do this.
It doesnt really make it any harder to program though
with non-det. udfs. I started my programming life on
ORACLE, where n.d. udfs are perfectly natural, and
it doesnt pose any problems.
The only time i remember it ever raising an issue was
if i wanted to create a function-based index (another
thing i miss in SS). But then you just have to carefully
code it so it is deterministic and you can also add a
compiler hint to tell the db that it is, which improves
performance then.
Sometimes that what you *want* to design...
non-determinism, its the whole point of the
function in some instances.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OfWL%23yYxFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi
> Some restrictions are removed in SQL Server 2005.
> It makes it a lot harder to architect and non-deterministic functions as
> each call to the function may return a different value.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
> news:uNVvk3WxFHA.3556@.TK2MSFTNGP12.phx.gbl...
>|||When would you ever want to design a non-deterministic function? If you
are doing set operations and return multiple rows, then it really makes
no sense, because basically you would really be asking for unreliable
results.
If the goal is to get unreliable results (for example a custom random
function), then I can understand it.
Please note, that if you pass in a timestamp, your UDF would probably
still be deterministic.
If you are planning to use the function on a one-row-at-a-time basis,
then you could just as well use a stored procedure (with output
parameters if needed) instead of a UDF.
So my question is: what application would you have for a non-determistic
UDF?
Gert-Jan
Arthur Dent wrote:
> Thanks for the info,
> Good to know well be able to do this.
> It doesnt really make it any harder to program though
> with non-det. udfs. I started my programming life on
> ORACLE, where n.d. udfs are perfectly natural, and
> it doesnt pose any problems.
> The only time i remember it ever raising an issue was
> if i wanted to create a function-based index (another
> thing i miss in SS). But then you just have to carefully
> code it so it is deterministic and you can also add a
> compiler hint to tell the db that it is, which improves
> performance then.
> Sometimes that what you *want* to design...
> non-determinism, its the whole point of the
> function in some instances.
[snip]