locked
Generate random number but.... RRS feed

  • Question

  • I want to know how can I generate a rancom number which does not exist in the random_number field so the number will be random but unique...
    Jassim Rahma
    Sunday, July 18, 2010 2:31 PM

Answers

  • WHILE 1 = 1
    BEGIN
       SELECT @x = checksum(newid())
       IF NOT EXISTS (SELECT *
                      FROM   tbl
                      WHERE  col = @x)
          BREAK
    END
    INSERT tbl (col) VALUES (@x)

    checksum(newid()) generates a number in the range -2147483648 to 2147483647.
    If you need a smaller range, you can easily scale it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Naomi N Sunday, July 18, 2010 3:58 PM
    • Marked as answer by KJian_ Friday, July 23, 2010 10:07 AM
    Sunday, July 18, 2010 3:39 PM

All replies

  • I do not understand your question completely, assuming you are working with an interger datatype for you random number column

    SELECT CAST(CAST(newid() AS binary(3)) AS int) this should generate random number 
    

     

    if you are doing an update in an existing column use the same expression in the update statement

     

    May be you should provide more info
    Sunday, July 18, 2010 2:45 PM
  • WHILE 1 = 1
    BEGIN
       SELECT @x = checksum(newid())
       IF NOT EXISTS (SELECT *
                      FROM   tbl
                      WHERE  col = @x)
          BREAK
    END
    INSERT tbl (col) VALUES (@x)

    checksum(newid()) generates a number in the range -2147483648 to 2147483647.
    If you need a smaller range, you can easily scale it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Naomi N Sunday, July 18, 2010 3:58 PM
    • Marked as answer by KJian_ Friday, July 23, 2010 10:07 AM
    Sunday, July 18, 2010 3:39 PM