System Function RAND() within UDF

Task: the system function RAND() use in the user defined function (UDF)
Solution: the system function RAND() is not possible to be used in the user defined function (see Figure 1 to 3). However, this function is possible to be implemented without problems into the stored procedure (see Figure 4 and 6), or to be used by means of a database object “view” ( see Figure 5 to 8 )

Figure 1: Definition of the Table-valued function

Figure 2: Result of SQL query (of the Table-valued and Scalar-valued function)

Figure 3: Definition of the Scalar-valued function

Figure 4: Definition of the stored procedure

Figure 5: definition of the view

Figure 6: Result of SQL query (of the stored procedure and view)

Figure 7: Definition of SQL query using the view

Note: command GO 5 provides the execution of programming batch 5 times

Figure 8: Result of SQL query using the view

Figure 9: Definition of SQL query with the pre-defined initialisation value

Figure 10: Result of SQL query with the pre-defined initialisation value

Note: when entering the initialisation value, an identical result of random value will always be generated

Additional reference:

• Generating Random Numbers in SQL Server Without Collisions
  by Aaron Bertrand