l have a telephone cleaning procedure that l wrote and have made a couple of modifications to make use of the phone code table so that l can validate are codes and prefixes on my telephone numbers. My problem is that my procedure does what its meant to do but is very slow. Is it because I'm using a cursor? l run it over record sets
of 2million sometimes?

How can l optimise it? How do reduce my code ? Please comment .

/*
---------------------------------------------------------------------------
Author: Raymond
Date : 3/12/02
Version : 2.0
Input Parameters : None
Usage : Create in the database That you want to run the Telephone Validation Routine in.
Alter the Table that has the Telephone Numbers to be Validated
-- Alter Table (Table Name) Add
-- Errno int NULL
,Errstr varchar (50)
,Telno varchar (15)
,NewTelNo varchar (20)
,NewTelType varchar (20)

Edit the Cursor then run it.

DECLARE @errno INT
DECLARE @errstr VARCHAR(100)
DECLARE @Telno VARCHAR(15)
DECLARE @NewTelNo VARCHAR(15)
DECLARE @NewTelType VARCHAR(20)
DECLARE @i BIGINT

SET @i = 0
DECLARE rs CURSOR FOR --local fast_forward for

SELECT tel1,newtelno,newteltype,errstr,errno
FROM Ray WHERE errstr IS NULL
OPEN rs
FETCH NEXT FROM rs INTO @TelNo,@NewTelNo,@NewTelType, @errstr, @errno
WHILE @@fetch_status = 0
BEGIN
SET @i = @i + 1
EXEC sp_Validate_Tel_No @Telno = @Telno, @NewTelNo = @NewTelNo output,
@NewTelType = @NewTelType output, @errno = @errno output, @errstr = @errstr output
BEGIN TRANSACTION
UPDATE Ray
SET Newtelno = @NewTelNo,
errstr = @errstr,
newteltype = @NewTelType,
ErrNo = @errno
WHERE current of rs
COMMIT
FETCH NEXT FROM rs INTO @TelNo, @NewTelNo,@NewTelType, @errstr, @errno
SELECT @i
END
CLOSE rs
DEALLOCATE rs
--*************************************************
---------------------------------------------------------------------------
*/

CREATE PROCEDURE dbo.Sp_Validate_TelNo_Dev

@Telno varchar(20),
@errno int output,
@errstr varchar(100) output,
@NewTelNo varchar(15) output,
@NewTelType varchar(20) output

AS

Set Nocount On
--inside variables
Declare @j int
Declare @i int
Declare @tmp varchar(20)


Set @errno = 0
Set @NewTelNo = null
Set @NewTelType = null
-------------------------------------------------------------------------------
--Step One: Check if Tel_No is totally blank --------------------------
If len(ltrim(rtrim(@Telno))) = 0 or @Telno is null
Begin
Set @errno = -1
Set @errstr = 'Invalid:Tel number is entirely blank'
Goto Doerror
End

-------------------------------------------------------------------------------
--Interim Step: Strip all the non-numeric characters from the TelNo string
set @i = 0
set @tmp = ''
while @i < len(@telno) +1
begin
set @i = @i + 1
if isnumeric(substring(@TelNo,@i,1))=1 and substring(@TelNo,@i,1) <> '$' and substring(@TelNo,@i,1) <> '.' and substring(@TelNo,@i,1) <> ','
begin
if substring(@TelNo,@i,1)/1 > 0
begin
set @tmp = @tmp + substring(@TelNo,@i,1)
end
if substring(@TelNo,@i,1) = '0'
begin
set @tmp = @tmp + substring(@TelNo,@i,1)
end

end
end
set @NewTelNo = left(@tmp,10)

-------------------------------------------------------------------------------
--Step Two: Check if tel contains any digits--------------------------
if len(ltrim(rtrim(@NewTelno))) = 0 or @NewTelno is null
begin
set @errno = -2
set @errstr = 'Invalid:Tel number contains no digits'
goto doerror
end
-------------------------------------------------------------------------------
--Step Three: Check if tel contains at least 7 digits--------------------------
if len(ltrim(rtrim(@NewTelno))) < 7
begin
set @errno = -3
set @errstr = 'Invalid:Tel number must contains at least 7 digits'
goto doerror
end

-------------------------------------------------------------------------------
--Step Four: Check that the tel no does not start with a zero or a one
if len(ltrim(rtrim(@NewTelno))) >= 7 And len(ltrim(rtrim(@NewTelno))) < 10 and left(@NewTelNo,1) < 2
begin
set @errno = -4
set @errstr = 'Invalid:Tel number has less than 10 digits.'
goto doerror
end

-------------------------------------------------------------------------------
--Step Five: Check if the tel no dailing code starts with a zero if the
-------------tel no is longer that 7 digits--------------------------
if len(ltrim(rtrim(@NewTelno))) > 7 and left(@NewTelNo,1) <> 0
begin
set @errno = -5
set @errstr = 'Invalid:Tel number dialing code must start with the digit zero'
goto doerror
end
else if len(ltrim(rtrim(@NewTelno))) = 7 and left(@NewTelNo,1) <> 0
begin
set @errno = -6
set @errstr = 'Invalid: No dialing code included'
goto doerror
end
-------------------------------------------------------------------------------
--Step Six: Check that the tel no do not start with a two zero digits like 00
if left(@NewTelNo,2) = '00'
begin
set @errno = -7
set @errstr = 'Invalid:Tel number may not start with two zero digits like 00'
goto doerror
end

-------------------------------------------------------------------------------
--Step Seven: Check that the tel no is 10 digits long if it starts with 08 or 07
if (left(@NewTelNo,2) = '08' or left(@NewTelNo,2) = '07') and len(@newtelno) <> 10
begin
set @errno = -8
set @errstr = 'Invalid:Tel number that start with 08 or 07 must be 10 digits long'
goto doerror
end

-------------------------------------------------------------------------------
--Step Eight: Check for toll free no's
if (left(@NewTelNo,4) = '0800'
or left(@NewTelNo,3) = '086'
or left(@NewTelNo,3) = '088'
or left(@NewTelNo,2) = '09' or left(@NewTelNo,3) = '089')
begin
set @errno = -9
set @errstr = 'Invalid: Toll Free Nos are not allowed'
goto doerror
end

-------------------------------------------------------------------------------
--Step Nine: Check that the tel nos starting with 08 or 07 is valid cell phone no's
if (left(@NewTelNo,2) = '08' or left(@NewTelNo,2) = '07')
and (substring(@newtelno,3,1) <2 or substring(@newtelno,3,1) > 4)
begin
set @errno = -10
set @errstr = 'Invalid: The third character of a 08 or 07 cell no may only be equal to 2,3 or 4'
goto doerror
end

-------------------------------------------------------------------------------
--Step Ten: Check that the fourth digit of a cell no do not start with a 0 or 1
-- Valid cell numbers were found with zero or 1 in forth digit
-- if (left(@NewTelNo,2) = '08' or left(@NewTelNo,2) = '07')
-- and (substring(@newtelno,4,1) < 2)
-- begin
-- set @errno = -10
-- set @errstr = 'Invalid: The fourth digit of a cell no may not start with a 0 or 1'
-- goto doerror
-- end
--************************************************** ***************************
-------------------------------------------------------------------------------
--Step Eleven: Check for more than 5 consecutive digits--------------------------
set @i = 0
set @j = 0
set @tmp = null
while @i < len(@NewTelNo) + 1
begin
set @i = @i + 1
if @i = 1
begin
set @tmp = substring(@NewTelNo, @i,1)
set @j = 1
end
else
begin
if @tmp = substring(@NewTelNo, @i,1)
begin
set @j = @j + 1
if @j > 5
begin
set @errno = -11
set @errstr = 'Invalid:Tel number contains more than 5 consequtive digits like 555555'
goto doerror
end
end
else
begin
set @tmp = substring(@NewTelNo, @i,1)
set @j = 1
end
end
end

--Update tel no type
if left(@newTelNo,2) = '08' or left(@newTelNo,2) = '07'
begin
set @newTelType = 'Cellular No'
end
else
begin
set @newTelType = 'Land Line'
end
----- Check The Area If Its Valid -------------------------------------------------
if @errno = 0 And @Newteltype = 'Land Line'
Begin
if Substring(@NewtelNo,1,3) not in (Select Area_Code from Phone_Codes)
begin
Set @errno = -11
set @errstr = 'This is an Invalid Area Code'
end
End
------------------------------------------------------------------------------------
----- Check the Prefix If its Valid ------------------------------------------------

if @errno = 0 And @Newteltype = 'Land Line'
Begin
if Substring(@NewtelNo,4,3) not in (Select LA From Phone_Codes
Where Area_Code = Substring(@NewtelNo,1,3))
begin
Set @errno = -12
set @errstr = 'This is an Invalid Prefix'
end
End
--print Substring(@NewtelNo,4,6)
------------------------------------------------------------------------------------

doerror:
if @errno <> 0
begin
set @NewTelNo = null
end

if @errno = 0
begin
set @errstr = 'All OK'
end
GO