Coffeehouse Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Error: Invalid use of 'rand' within a function.

Back to Forum: Coffeehouse
  • User profile image
    pierlove

    Any assistance with this would be appreciated.

    http://prodevstudios.com/blog/archive/2004/05/05.aspx

    Thanks - JB.

  • User profile image
    object88

    An MSDN example show RAND being used like this:

    SELECT RAND()

    So maybe you need to do soemthing like

    SELECT @random_number (SELECT RAND())

    And then apply @random_number in the ROUND function.

    I haven't tried this, so besure to get yourself a grain of salt with this "advice".  Smiley

  • User profile image
    pierlove

    object88 wrote:

    I haven't tried this, so besure to get yourself a grain of salt with this "advice".  Smiley


    Thanks for your post, however this produces the same error when executed within a Function.

    -JB.

  • User profile image
    bitmask

    There are ways around this, for instance, you could create a view to return SELECT RAND(), and SELECT from the view in your UDF.

    But, just remember SQL is a set based language. Making a non-deterministic function can be a scalability problem as SQL will need to reevaluate the function for each row in a a SELECT, UPDATE, DELETE.

  • User profile image
    pierlove

    bitmask wrote:

    But, just remember SQL is a set based language. Making a non-deterministic function can be a scalability problem as SQL will need to reevaluate the function for each row in a a SELECT, UPDATE, DELETE.



    bitmask, thanks.  I agree. And that will not do.
    There has to be a way to use Rand() in a UDF that is valid. Hence the error:

    Invalid use of 'rand' within a function.

    The error implies a valid use would (theoretically) exist.

    -JB

  • User profile image
    pierlove

    SOURCE:
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_460j.asp

    The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

    Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:

    @@CONNECTIONS @@PACK_SENT GETDATE
    @@CPU_BUSY @@PACKET_ERRORS GetUTCDate
    @@IDLE @@TIMETICKS NEWID
    @@IO_BUSY @@TOTAL_ERRORS RAND
    @@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
    @@PACK_RECEIVED @@TOTAL_WRITE  




    BUMMER!
  • User profile image
    Bryn Waibel

    I don't believe that use of Rand is ever allowed inside a UDF. Why not just add a paramter to the UDF?

    CREATE FUNCTION [dbo].[RandomDate]
    (@rand float)
    ...

    select [dbo].[RandomDate]( rand() )

    -Bryn

  • User profile image
    pierlove

    Excellent!   Thank you! Bryn.

    That works great, I will post solution to my blog.

    Thanks again.  - JB

  • User profile image
    bitmask

    Just be aware if you do:

      UPDATE MyTAble
        SET MyNumber = SELECT dbo.RandomDate(RAND())

    Then every row updated will have the same random date.

    If you need a random date for every row (hopefully you don't, or this is a one time run), you can use a view as I suggested earlier:

      CREATE VIEW RandomNumber
      AS
        SELECT RAND() AS number

    Then inside of your UDF:

      SELECT number FROM RandomNumber

    will give you a random number. Just remember the caveats from my first post.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.