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

Conversation locked

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