Generate random number between Two number

Question

Hi,

I want generate a number between two number.

For example:

  1970<a<2000

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.