# Generate random number between Two number

• ### Question

• Hi,

I want generate a number between two number.

For example:

1970<a<2000

Wednesday, April 24, 2013 8:40 PM

### All replies

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

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

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 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