Random Number Procedure Problems!

Hey Guys, I have a quick stored
procedure question for you. I created a procedure that returns a randomly
generated number.

I know there is a built in
Rand() function but I need to be able to
specify the range. Anyways, it€™s returning multiple numbers (1 for each row in
the tempTable)
But I just want one number returned.
I tried using MAX( ) but that€™s not a good way of doing it.


My question is, how do I return just
one record or number?

Thanks
Fellas,

Chris


ALTER PROCEDURE
dbo.spx_GetRandomNum

AS

DECLARE @Start_Num int

DECLARE @End_Num int

DECLARE @ReturnValue
int
DECLARE @RecNum
int
DECLARE @CurrentRec
int
DECLARE @MyCount
int

SET @Start_Num=1

SET
@End_Num=100


CREATE TABLE #tblTemp (CurrentRecNo
int IDENTITY(1,1), RandomNumber int null)

SET @MyCount = ( @End_Num -
@Start_Num ) + 1
SET @CurrentRec = 1


WHILE @CurrentRec <= @MyCount


BEGIN
SET @RecNum
= CAST((RAND() * @MyCount) AS int) + @Start_Num

INSERT INTO
#tblTemp (RandomNumber) VALUES (@RecNum)
SET
@CurrentRec = @CurrentRec + 1

END

SET
@ReturnValue = (SELECT RandomNumber FROM
#tblTemp)

DROP TABLE #tblTemp


RETURN
@ReturnValue
GO