Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2001
    Location
    India
    Posts
    25

    Unanswered: UDF and GetDate()

    Hi,

    I created an UDF with datetime as parameter which will return a recordset.
    I need to do pass getdate() as a parameter to that UDF.
    But I am not able to do.
    Can anyone give me a soln?

    Thanks in advance,
    Selva Balaji B.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Try setting it as a variable then pass that.

    I get an error too when returning a table but not with a scalar.
    I have not put any service packs on though - maybe sp2 would help.

  3. #3
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    GetDate() doesn't work in UDFs!

    I am very suprised to find that a SELECT statement that has a WHERE clause that uses GetDate() works fine outside a UDF but not within the UDF.

    To try & get round this, I declared a variable within the UDF as datetime, and set its value to GetDate(). But this too was rejected.

    I can't believe that GetDate() cannot be referenced within UDFs. Can anyone help or shed some light on this, please?

    Regards
    Alex

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    A udf has to give the same response given the same parameters if no change to the database.
    getdate() is non-deterministic therefore if used within a udf it will give different results depending on the time ran.
    This condition is imposed so that the query can be optimised from the udf inputs and doesn't rely on the code inside the udf.

    You can get around this by creating a view
    create view currenttime
    as
    select getdate()
    go

    You can seemingly access this happily from within the udf as the udf compilation doesn't check the contents of the view (an oversight and indication why udf's are so slow).
    This may appear to work and will in some conditions but will have interesting affects on unions, group by's and distincts as theserver will make the mistake of thinking that it can tell the different results that will be obtained by the parameters input.

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Because view trick is a bug, I recomend to pass getdate() as parameter to function.

  6. #6
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    GetDate problems with UDFs

    Thanks Isapaleny,

    Setting the date outside the function, and passing it as a parameter worked fine.

    You're proving much better & useful than my SQL-SErver ref.books which somehow never seem to address real coding scenarios, and avoid relevant examples like the plague.

    Thanks once again,
    Best regards,
    Alex

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Originally posted by nigelrivett
    Try setting it as a variable then pass that.
    Odd, I thought I said that earlier.

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    I tried to make Packbella sure, which of your 2 answers choose.
    Your trick with view is nice and I have never heard about it before, but I expect problems with future versions of MSSQLServer.

    Have a nice weekend !

  9. #9
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    As I said
    You can seemingly access this happily from within the udf as the udf compilation doesn't check the contents of the view (an oversight and indication why udf's are so slow).
    This may appear to work and will in some conditions but will have interesting affects on unions, group by's and distincts as theserver will make the mistake of thinking that it can tell the different results that will be obtained by the parameters input.


    I wouldn't recommend anyone doing this as it obviously causes problems with the execution plan - but some people do use it at their own risk.

  10. #10
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    UDF & GetData()

    Sorry Nigel,

    Yes the answer did indeed embody your 1st suggestion, and I should have acknowledged this in my reply to Isapaleny whose response persuaded me to play safe and stick with the passing of the datetime variable to the UDF.

    It seems we "newbies" now have the luxury to choose solutions to our problems from experts like yourselves, when we've been let down by commercially available reference books. Anyway, thanks very much Nigel for your input, and also a big thanks to "dBforums" for providing this great facility which on more than one occasion has got me out of the sh*t! (or put more genteely: "...out of this quagmire of ignorance"!)

    Best regards
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •