Use of Rand() in a UDF(User-Defined function)

• msramanujam

  SSC Enthusiast

  Points: 138

  More actions

  March 11, 2002 at 6:29 am

  #101094

  We have a necessity to call the Rand() in a UDF.But as per SQL Server 2000, it refuses to allow the usage of RAND(), as it is non-deterministic.

  Hence, we tried by using a stored procedure which has a call to the RAND() and return the subsequent random value.

  This again fired back, as there is no provision to call a normal stored procedure from within a function(UDF). As SQL Server allows only functions and extended Strored Procedures to be used.

  Is there a way out to get the right stuff.Kindly reply.

  HTH

  Ramanujam

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 12, 2002 at 5:12 am

  #425244

  I don't believe there is a way. If you figure out how to call an extended stored procedure so you can get its results then I have an xp I wrote for random numbers which I can provide but in BOL it looks like you cannot return the data directly back thru the xp.

  quote:

  ---

  The extended stored procedure, when called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL. The extended stored procedure could connect back to Microsoft® SQL Server™; however, it should not attempt to join the same transaction as the function that invoked the extended stored procedure.

  ---

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)