I need to build an SQL stored procedure which basically updates an existing table (of about 150,000 rows) with an ID.

The table that this stored procedure will run over is basically a list of people, their names, addresses etc.

Now the algorithm for the id of the person is as follows:
- Take up to the first 4 characters of the persons first name.
- Take up to the first 2 characters of the persons last name.
- Pad the rest with 0's, with a counting number at the end, until the field is 8 characters.

For instance, the name JOHN SMITH would have an ID of 'JOHNSM00'. If there were 2 JOHN SMITH's, the ID of the next person would be JOHNSM01. If the persons name was FI LYNN for instance, the ID would be FILY0000.

I've got the following stored procedure that I wrote, but it takes around 9 hours to run! Is there a better way of doing this that I am missing?

```
ALTER PROCEDURE [dbo].[LM_SP_UPDATE_PERSON_CODES]
AS

DECLARE @NAMEKEY NVARCHAR(10)
DECLARE @NEWNAMEKEY NVARCHAR(10)
DECLARE @LENGTH INT
DECLARE @KEYCOUNT INT
DECLARE @I INT
DECLARE @PADDING NVARCHAR(8)
DECLARE @PERSONS CURSOR
DECLARE @FIRSTNAME NVARCHAR(30)
DECLARE @LASTNAME NVARCHAR(30)

SET @PADDING = '00000000'
--FIRST CLEAR OLD NEW NAMEKEYS IF ANY EXIST
UPDATE LM_T_PERSONS SET NEW_NAMEKEY = NULL
SET @PERSONS = CURSOR FOR
SELECT NAMEKEY, NAME_2, NAME_1 FROM LM_T_PERSONS

OPEN @PERSONS
FETCH NEXT FROM @PERSONS INTO @NAMEKEY, @FIRSTNAME, @LASTNAME

WHILE @@FETCH_STATUS = 0
BEGIN
    --CHECK THE LENGTH OF FIRST NAME TO MAKE SURE NOTHING EXCEEDS 4
    SET @LENGTH = LEN(@FIRSTNAME)
    IF @LENGTH > 4 
        SET @LENGTH = 4

    SET @NEWNAMEKEY = SUBSTRING(@FIRSTNAME,1,@LENGTH)

    --CHECK THE LENGTH OF LAST NAME TO MAKE SURE NOTHING EXCEEDS 2
    SET @LENGTH = LEN(@LASTNAME)
    IF @LENGTH > 2
        SET @LENGTH = 2

    SET @NEWNAMEKEY = @NEWNAMEKEY + SUBSTRING(@LASTNAME,1,@LENGTH)
    SET @LENGTH = LEN(@NEWNAMEKEY)
    SET @I = 0
    SET @PADDING = SUBSTRING('00000000',1,8 - LEN(@NEWNAMEKEY) - LEN(CONVERT(NVARCHAR(8),@I)))
    --SEE IF THIS KEY ALREADY EXISTS
    SET @KEYCOUNT = (SELECT COUNT(1) FROM LM_T_PERSONS WHERE NEW_NAMEKEY = @NEWNAMEKEY + @PADDING + CONVERT(NVARCHAR(8),@I) )
    WHILE @KEYCOUNT > 0
    BEGIN
        SET @I = @I+1
        SET @PADDING = SUBSTRING('00000000',1,8 - LEN(@NEWNAMEKEY) - LEN(CONVERT(NVARCHAR(8),@I)))
        SET @KEYCOUNT = (SELECT COUNT(1) FROM LM_T_PERSONS WHERE NEW_NAMEKEY = @NEWNAMEKEY + @PADDING + CONVERT(NVARCHAR(8),@I) )
    END
    UPDATE LM_T_PERSONS SET NEW_NAMEKEY = @NEWNAMEKEY + @PADDING + CONVERT(NVARCHAR(8),@I) WHERE NAMEKEY = @NAMEKEY

    FETCH NEXT FROM @PERSONS INTO @NAMEKEY, @FIRSTNAME, @LASTNAME
END
CLOSE @PERSONS
DEALLOCATE @PERSONS

```