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