if object_Id( 'dbspScrubAreaCodes') Is Not Null
drop procedure dbspScrubAreaCodes
go

CREATE procedure dbspScrubAreaCodes
as
/*
*************************************************************
Name: dbspScrubAreaCodes
Description:
   Process to search through member profile for members which
have a areacode of NULL and attempt to reparse the phone number
into an areacode and number. If a parse can be made, then the
areacode field is updated.
  Writes the total number of rows to the DBALog

Usage:exec dbspScrubAreaCodes

Author: Steve Jones
			Copyright 2000 dkranch.net

Input Params:
-------------

Output Params:
--------------

Return: @@error

Results:
---------

Locals:
--------
@err		Holds error value

Modifications:
--------------

*************************************************************
*/
set nocount on
declare	@err int,
			@rowcnt int

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @err = -1
begin
  Raiserror( 'Parameter Error:Usage:exec dbspScrubAreaCodes', 12, 1)
  return @err
end

select @rowcnt = 0

update userdetail
set areacode = substring( phone, 1, 3)
where areacode Is Null
and len( phone) = 10

select @rowcnt = @@rowcount

update userdetail
set areacode = substring( phone, 1, 3)
where areacode Is Null
and len( phone) = 12
and substring( phone, 4, 1) in ( '-', '/', '.')
and substring( phone, 8, 1) in ( '-', '.')

select @rowcnt = @rowcnt + @@rowcount

update userdetail
set areacode = substring( phone, 2, 3)
where areacode Is Null
and len( phone) = 14
and substring( phone, 1, 1) = '('
and substring( phone, 5, 1) = ')'

select @rowcnt = @rowcnt + @@rowcount

update userdetail
set areacode = substring( phone, 2, 3)
where areacode Is Null
and len( phone) = 13
and substring( phone, 1, 1) = '('
and substring( phone, 5, 1) = ')'

select @rowcnt = @rowcnt + @@rowcount

insert DBALog (Cat, Msg)
values ( 'AreaCodes Parsed', cast(@rowcnt as char( 10)))

return @err
GO
grant execute on dbspScrubAreaCodes to WebUser
go
if object_id( 'dbspScrubAreaCodes') Is Null
select 'dbspScrubAreaCodes Not Created'
else
select 'dbspScrubAreaCodes Created'
go