February 5, 2009 at 11:07 pm
I am trying to update a table with Random Names from a short list, into a table column with about 1000 names. I'd like to deidentify my Patient Table, so I can open up the system, and no worry about sensitive patient names being seen. The SQL I am using is:
ALTER function random_names()
RETURNS nchar(10)
AS
BEGIN
DECLARE @f nchar(10)
set @f = (SELECT TOP (1) Name FROM dbo.TestNames ORDER BY NewId())
return @f
END
go
update TBL_PATIENT SET First_NAME = dbo.random_names()
But I keep getting the error:
Msg 443, Level 16, State 1, Procedure random_names, Line 6
Invalid use of side-effecting or time-dependent operator in 'newid' within a function.
David
February 6, 2009 at 12:59 am
HI,
U CAN NOT USE NEWID FUNCTION WITHIN THE FUNCTION LIKE THAT.
U HAVE TO USE VIEW TO WORK UR FUNCTION. SEE CRETAE ONE VIEW LIKE
CREATE VIEW A
AS
SELECT Name,NEWID() AS NEW_ID FROM dbo.dbo.TestNames
AND THEN IN UR FUNCTION
ALTER function random_names()
RETURNS nchar(10)
AS
BEGIN
DECLARE @f nchar(10)
set @f = (SELECT TOP (1) Name FROM dbo.TestNames
ORDER BY New_Id)
return @f
END
go
THIS WILL SURLY WORK.....
LET ME KNOW IF U FIND ANY PROBLEM........
MITHUN
February 6, 2009 at 3:28 am
CREATE FUNCTION dbo.fnRandomNames
(
@uuid UNIQUEIDENTIFIER
)
RETURNS NCHAR(10)
AS
BEGIN
RETURN(
SELECT TOP 1Name
FROMdbo.TestNames
WHEREID = 1 + ABS(CHECKSUM(@uuid)) % 1000
)
END
UPDATETBL_PATIENT
SETFirst_NAME = dbo.fnRandomNames(NEWID())
N 56°04'39.16"
E 12°55'05.25"
February 6, 2009 at 2:29 pm
Thanks Mithun,
I actually used the code in the post below yours, and it worked.
David
February 6, 2009 at 2:30 pm
Thanks so much for showing me this, saved hours of frustration.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply