mssql update seems to be happening too quickly for function

Greetings.  I have a function I have written to generate a unique key / purl for each record in one of my tables.  below in the "code" is my function.

the function works well, except when i run:
UPDATE    ICM_GUAMaster
SET              PURL = dbo.funcGenerateUniquePURLByName(firstname, lastname)
where PURL is null

things seem to be happening too quickly.  i end up with firstnamelastname for all of my records instead of firstnamelastname, firstnamelastname1, etc.

any help would be appreciated.

alter FUNCTION funcGenerateUniquePURLByName
(
	-- Add the parameters for the function here
	@FirstName as varchar(200),
	@LastName as varchar(200)
)
RETURNS varchar(200)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Count as int
	Set @Count = 0
	DECLARE @NewPURL as varchar(200)
	SET @FirstName = CAST(REPLACE(@FirstName,' ','') as varchar(100))
	SET @LastName = CAST(REPLACE(@LastName, ' ', '') as varchar(100))
	--SET @NewPURL = (select 'JohnHENDERSON' as expr1)
	SET @NewPURL = @FirstName+@LastName
 
 
	-- Add the T-SQL statements to compute the return value here
while ((select count(PURL) from ICM_GUAMaster With (tablockx, holdlock) where PURL = @NewPURL) = 1)
	begin
	set @Count=@Count+1
	set @NewPURL = (REPLACE(@FirstName,' ','')+REPLACE(@LastName, ' ', '')+cast(@Count as varchar(20)))
	end
 
	-- Return the result of the function
	RETURN @NewPURL
 
END
GO

Open in new window

rugby148Asked:
Who is Participating?
 
brad2575Commented:
No its not that it is happening to fast (it would never do that, it always waits for the function to return the value first) but since you are doing a bulk update it is finding the value to insert based off the data at the time of the query (as if you were doing a select).

You need to do your query as a loop or something and do one record at a time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.