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:
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)
	-- 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)
	set @Count=@Count+1
	set @NewPURL = (REPLACE(@FirstName,' ','')+REPLACE(@LastName, ' ', '')+cast(@Count as varchar(20)))
	-- Return the result of the function

Open in new window

Who is Participating?
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.
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.