title: SQL Server function error (can't I use NewID() in a SQL Server function?)
body: <p>I wrote this function that generates random float numbers and update the table but I get this error : </p>
  
  <blockquote>
    <p>Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 30<br>
    Invalid use of a side-effecting operator 'newid' within a function.<br>
    Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 30<br>
    Invalid use of a side-effecting operator 'rand' within a function.<br>
    Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 31<br>
    Invalid use of a side-effecting operator 'newid' within a function.<br>
    Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 31<br>
    Invalid use of a side-effecting operator 'rand' within a function.</p>
  </blockquote>
  
  <p>Can't I use <code>newid()</code> or <code>Rand()</code> in a function?</p>
  
  <p>Here's my code :</p>
  
  <pre><code>Create FUNCTION Update_Driver_Positon()
  RETURNS  @rtnTable TABLE 
  (
      -- columns returned by the function
      DriverID UNIQUEIDENTIFIER NOT NULL,
      UserID numeric(18,0)  NULL,
      Employ_Date Date null,
      Car_Type_ID numeric(18,0),
      is_Active bit null,
      Pos_X numeric(18,3),
      Pos_Y numeric(18,3)
  )
  AS
  BEGIN
  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 
  
  --This select returns data
  insert into @rtnTable
  SELECT * FROM driver_tbl 
  return
  END
  </code></pre>
  
  <p>and I also tried to use a stored procedure but that didn't work, the random numbers that suppose to update the table's field they were identical in every record and also couldn't Exec the sp more than once here's my SP : </p>
  
  <pre><code>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
  ')
  </code></pre>
  
  <p>and exec code:</p>
  
  <pre><code> Exec dbo.Update_Driver_Position '##Temp'
   Select * From ##Temp
  </code></pre>