CODEWITHSUNDEEP
sql-server-2008function
DeadlyDagger
DeadlyDagger

Reputation: 609

SQL Server function error (can't I use NewID() in a SQL Server function?)

I wrote this function that generates random float numbers and update the table but I get this error :

Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 30
Invalid use of a side-effecting operator 'newid' within a function.
Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 30
Invalid use of a side-effecting operator 'rand' within a function.
Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 31
Invalid use of a side-effecting operator 'newid' within a function.
Msg 443, Level 16, State 1, Procedure Update_Driver_Positon, Line 31
Invalid use of a side-effecting operator 'rand' within a function.

Can't I use newid() or Rand() in a function?

Here's my 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

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 : 

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

and exec code:

 Exec dbo.Update_Driver_Position '##Temp'
 Select * From ##Temp

Upvotes: 0

Views: 5364

Answers (2)

Pavlo Neiman
Pavlo Neiman

Reputation: 7536

Create a VIEW using RAND function.

CREATE VIEW rndView
AS
SELECT RAND() rndResult
GO

Create a UDF using the same VIEW.

CREATE FUNCTION RandFn()
RETURNS DECIMAL(18,18)
AS
BEGIN
DECLARE @rndValue DECIMAL(18,18)
SELECT @rndValue = rndResult
FROM rndView
RETURN @rndValue
END
GO

Now execute the UDF and it will just work fine and return random result.

SELECT dbo.RandFn()
GO

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453067

You can't use either of those in a function.

There are workarounds (e.g. including them in a View definition and selecting from the View is allowed in a function) but I see your next line uses UPDATE which is also disallowed in a function so you will need to use a different approach for this. Perhaps a stored procedure.

Upvotes: 2

Related Questions