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

Any assistance with this would be appreciated.

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

Thanks - JB.

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

object88 wrote:

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

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

-JB.

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.

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

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!

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

Excellent!   Thank you! Bryn.

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

Thanks again.  - JB

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.