T SQL procedure to update data

-- Counter
DECLARE @pos_cnt int
DECLARE @ok_cnt int
DECLARE @error_cnt int
DECLARE @cntry_code_start_pos int
DECLARE @cntry_code_len int

-- Converted Phone
DECLARE @new_phone varchar(24)
-- Valid Phone Flag
DECLARE @phone_data_ok varchar(1)
-- Valid Country Code Flag
DECLARE @valid_coutry_code varchar(1)

DECLARE @emplid varchar(11)
DECLARE @phone_type varchar(4)
DECLARE @phone varchar(24)
DECLARE @local_phone varchar(24)
DECLARE @country_code varchar(6)
DECLARE @country_code_to_check varchar(6)

-- Declare a temp table
DECLARE @phone_table TABLE (
EMPLID varchar(11),
PHONE_TYPE varchar(4),
NGB_LOCAL_PHONE varchar(18),
MESSAGE varchar(100)
)

SET NOCOUNT ON

declare PhoneData cursor
for select EMPLID, PHONE_TYPE, PHONE, NGB_LOCAL_PHONE, NGB_INTL_DIAL_CD from PS_PERSONAL_PHONE
WHERE PHONE <> ' ' AND PHONE NOT LIKE '%@%'
--AND EMPLID = '16933'
for update

open PhoneData
fetch next from PhoneData INTO @emplid, @phone_type, @phone, @local_phone, @country_code

-- initialise counters
set @ok_cnt = 0
set @error_cnt = 0
while @@fetch_status = 0
begin
-- Check each position of the NGB_LOCAL_PHONE field.
SET @pos_cnt = 1
SET @new_phone = ''
SET @phone_data_ok = 'Y'
SET @phone = LTRIM(@phone)
-- process each position from the Phone field
WHILE @pos_cnt <= 24 BEGIN
-- if it is a number or space, then it is OK
IF CHARINDEX(SUBSTRING(ltrim(@phone), @pos_cnt, 1),' 0123456789') > 0
OR (SUBSTRING(LTRIM(@phone),@pos_cnt,1) = '+' AND @pos_cnt = 1)
OR SUBSTRING(LTRIM(@phone),@pos_cnt,1) = ' ' begin
set @new_phone = @new_phone + SUBSTRING(LTRIM(@phone),@pos_cnt,1)
end
else begin
-- if it is ./()- or non breaking space then replace it with space
IF CHARINDEX(SUBSTRING(ltrim(@phone), @pos_cnt, 1),'./()-') > 0 or
ascii(SUBSTRING(ltrim(@phone), @pos_cnt, 1)) = 160 begin
set @new_phone = @new_phone + ' '
end
-- otherwise it is an error
else begin
SET @phone_data_ok = 'N'
print 'Invalid character ' + SUBSTRING(ltrim(@phone), @pos_cnt, 1) + ' / ASCII ' + convert(char(3), ascii(SUBSTRING(ltrim(@phone), @pos_cnt, 1))) + ' for EMPLID ' + @emplid + ' PHONE_TYPE ' + @phone_type + ' on Pos# ' + convert(char(2), @pos_cnt)
set @pos_cnt = 25
end
end
-- print '@pos_cnt ' + convert(char(2),@pos_cnt) + ' @new_phone ' + @new_phone
set @pos_cnt = @pos_cnt + 1
END
-- remove any leading spaces
set @new_phone = ltrim(@new_phone)
set @country_code = ' '
-- check if the Phone has leading + or 00
set @cntry_code_start_pos = 0
if left(@new_phone,1) = '+' begin
set @cntry_code_start_pos = 2
end
if left(@new_phone,2) = '00' begin
set @cntry_code_start_pos = 3
end
-- if there was an international dialing code present, then try to validate
if @cntry_code_start_pos > 0 begin
set @cntry_code_len = 5
WHILE @cntry_code_len >= 1 BEGIN
set @country_code_to_check = SUBSTRING(@new_phone ,@cntry_code_start_pos,@cntry_code_len)
-- print 'Validating Country code ' + @country_code_to_check + ' for @new_phone ' + @new_phone
set @valid_coutry_code = 'N'
select @valid_coutry_code = 'Y', @country_code = NGB_INTL_DIAL_CD from PS_NGB_INTL_DIALCD B
WHERE @country_code_to_check = B.NGB_INTL_DIAL_CD
AND LEN(B.NGB_INTL_DIAL_CD) = @cntry_code_len
if @valid_coutry_code = 'Y' begin
set @new_phone = ltrim(SUBSTRING(@new_phone, @cntry_code_start_pos + @cntry_code_len , 24))
-- print 'Valid Country code for @new_phone ' + @new_phone + ' @country_code ' + @country_code + ' @new_phone ' + @new_phone + ' @cntry_code_start_pos ' + convert(char(2), @cntry_code_start_pos) + ' @cntry_code_len ' + convert(char(2), @cntry_code_len)
set @cntry_code_len = 0
end
set @cntry_code_len = @cntry_code_len - 1
end
end
if @phone_data_ok = 'Y' and len(@new_phone) <= 17 begin
-- print 'Updating for EMPLID ' + @emplid + ' PHONE_TYPE ' + @phone_type + ' from PHONE ' + @phone + ' to NGB_LOCAL_PHONE ' + @new_phone + ' NGB_INTL_DIAL_CD ' + @country_code
update PS_PERSONAL_PHONE SET NGB_LOCAL_PHONE = @new_phone, NGB_INTL_DIAL_CD = @country_code
WHERE EMPLID = @emplid
and PHONE_TYPE = @phone_type

set @ok_cnt = @ok_cnt + 1
end
else begin
set @error_cnt = @error_cnt + 1
end
fetch next from PhoneData INTO @emplid, @phone_type, @phone, @local_phone, @country_code
end
print 'Update Count ' + convert(char(7), @ok_cnt) + '. Error Count ' + convert(char(7), @error_cnt)
close PhoneData
deallocate PhoneData