locked
Generate random number between Two number RRS feed

  • Question

  • Hi,

    I want generate a number between two number.

    For example:

      1970<a<2000

    Wednesday, April 24, 2013 8:40 PM

Answers

All replies

  • Please see the link

    http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Wednesday, April 24, 2013 8:41 PM
  • Please see the link

    http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    CREATE FUNCTION Random (@Upper INT,@Lower INT)
    RETURNS INT
    AS
    BEGIN
    DECLARE @Random INT
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    RETURN @Random
    END;

    Msg 443, Level 16, State 1, Procedure Random, Line 6
    Invalid use of a side-effecting operator 'rand' within a function.

    Thursday, April 25, 2013 6:19 AM
  • SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

    SELECT RAND() AS [RandomNumber]

    For more example please click :URL

    Thanks

    Tuesday, April 30, 2013 5:27 AM
  • Please see the link

    http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    CREATE FUNCTION Random (@Upper INT,@Lower INT)
    RETURNS INT
    AS
    BEGIN
    DECLARE @Random INT
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    RETURN @Random
    END;

    Msg 443, Level 16, State 1, Procedure Random, Line 6
    Invalid use of a side-effecting operator 'rand' within a function.

    RAND() is a built-in non-deterministic function and cannot  be used in user defined functions , refer the link http://msdn.microsoft.com/en-IN/library/ms191007.aspx#ValidStatements

    You can try another approach as below, 

    CREATE FUNCTION Random (@Upper INT,@Lower INT, @randomvalue numeric(18,10))
    RETURNS INT
    AS
    BEGIN
    DECLARE @Random INT
    SELECT @Random = ROUND(((@Upper - @Lower -1) * @randomvalue + @Lower), 0)
    RETURN @Random
    END;
    
    select dbo.Random(1,2,RAND())


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Tuesday, April 30, 2013 5:39 AM added another approach
    Tuesday, April 30, 2013 5:36 AM
  • Msg 443, Level 16, State 1, Procedure FUN_GET_RANDOM_INT_BETWEEN, Line 6
    Invalid use of a side-effecting operator 'rand' within a function.
    Tuesday, December 29, 2015 1:57 PM