|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Non-deterministic UDF's?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 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 -- Show quote-oj "Arthur Dent" <hitchhikersguideto-n***@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. Show quote "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-n***@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 >> > > 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: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Arthur Dent" <hitchhikersguideto-n***@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... >> 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-n***@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 >>> >> >> > > 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. Show quote "Mike Epprecht (SQL MVP)" <m***@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: m***@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Arthur Dent" <hitchhikersguideto-n***@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... >>> 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-n***@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 >>>> >>> >>> >> >> > > 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: Show quote > > 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] Reading the value from a view can end up having a serious impact on
performance. Consider supplying the datatime value through a parameter. ML  |
|||||||||||||||||||||||
Other interesting topics