SQL Server RandomNumbers Stored Procedure Error

Question

I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record.

CREATE PROCEDURE Update_Driver_Position
@TempName VARCHAR(8000)
AS
DECLARE @maxvalueX INT
DECLARE @minvalueX INT
DECLARE @maxvalueY INT
DECLARE @minvalueY INT

DECLARE @X INT  
DECLARE @Y INT


SET @maxvalueX = 3357
SET @minvalueX = 273
SET @maxvalueY = -320
SET @minvalueY = -3096


SET @X = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX
SET @Y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

UPDATE driver_tbl SET pos_x=@x , pos_y=@y 

EXEC ('
Select *
Into '+@TempName+'
From  dbo.Driver_Tbl
')

I also couldn't EXEC the stored procedure more than once. Here is that call;

 EXEC dbo.Update_Driver_Position '##Temp'
 SELECT * FROM ##Temp

Any ideas ?

Answer 1

Because you evaluate @X and @y once before the update.

You need to do in the UPDATE itself

Update driver_tbl 
set 
    pos_x = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueX - @minvalueX),0) + @minvalueX,
    pos_y = ROUND(RAND(CHECKSUM(NEWID())) * (@maxvalueY - @minvalueY),0) + @minvalueY

The answer I gave on your SO question shows this: http://stackoverflow.com/q/8546985/27535

Answer 2

I'd also recommend you avoid SQL Injection by doing:

Exec ('
Select *
Into '+QUOTENAME(@TempName)+'
From  dbo.Driver_Tbl
')

Otherwise, someone could put:

Exec dbo.Update_Driver_Position '##Temp FROM dbo.Driver_Tbl; DROP TABLE Driver_Tbl;--'

...and the reason you couldn't execute it more than once is probably because you had already created the table. You would need to drop ##Temp before you run it a second time.