Coffeehouse Thread

9 posts

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

Back to Forum: Coffeehouse
  • User profile image
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • 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.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.