Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 ?

share|improve this question

2 Answers 2

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

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

 
discard

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.