• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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

0
rugby148
Asked:
rugby148
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now