Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)
DECLARE @maxvalueX INT
DECLARE @minvalueX INT
DECLARE @maxvalueY INT
DECLARE @minvalueY 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 

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'

Any ideas ?

share|improve this question
up vote 3 down vote accepted

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

You need to do in the UPDATE itself

Update driver_tbl 
    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:

share|improve this answer
ThanX A lot ,it works perfectly. – rN uǝɹɐ Dec 19 '11 at 8:43

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.