locked
problem with rand function sql server 2008 RRS feed

  • Question

  • I use this code to generate random number then use this number but when I excute it ,I had this error

    "Invalid use of a side-effecting operator 'rand' within a function"

     

    Create FUNCTION [dbo].[GetUniqueStudentCode]()
    RETURNs int
    AS
    BEGIN

    WHILE (1=1)
      BEGIN
    DECLARE @Random INT;
    DECLARE @Upper INT;
    DECLARE @Lower INT
    Declare @student_count INT ;
    Declare @student_count1 INT ;
    Declare @mod_random_studentcount INT ;

    ----- This will create a random number between 0 and 10000000
    SET @Lower = 0 ---- The lowest random number
    SET @Upper = 10000000 ---- The highest random number
    SELECT @Random = CONVERT(INT, (@Upper-@Lower + 1)*RAND()+ @Lower);

    ---- This will return a mod of random and studentcount* 10
    SELECT @student_count = COUNT(*) from Student ;
    SELECT @student_count1 = @student_count * 100  ;
    SELECT @mod_random_studentcount = @Random % @student_count1;

    IF 0 =  (SELECT COUNT(*) FROM Student WHERE Code = @mod_random_studentcount)
    BEGIN
    return(@mod_random_studentcount);
    break;
    END

    END
    return(-1);
    END

    Tuesday, February 15, 2011 10:50 AM