USP_ADDRESS_UPDATECOORDINATES

Updates the latitude and longitude values for a given address.
Parameters

Parameter	Parameter Type	Mode
@ADDRESSID	uniqueidentifier	IN
@LATITUDE	decimal(16, 13)	IN
@LONGITUDE	decimal(16, 13)	IN
@PENDINGGEOCODE	bit	IN
@INVALIDGEOCODE	bit	IN
@MATCHCODE	nvarchar(50)	IN
@MATCHEDMETHOD	nvarchar(50)	IN
@GEOCODEEXCEPTION	nvarchar(255)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@REGEOCODE	bit	IN
Definition

Copy


            CREATE procedure dbo.USP_ADDRESS_UPDATECOORDINATES(
                @ADDRESSID uniqueidentifier,
                @LATITUDE decimal(16,13) = 0,
                @LONGITUDE decimal(16,13) = 0,
                @PENDINGGEOCODE bit = 0,
                @INVALIDGEOCODE bit = 0,
                @MATCHCODE nvarchar(50) = '',
                @MATCHEDMETHOD nvarchar(50) = '',
                @GEOCODEEXCEPTION nvarchar(255) = '',
                @CHANGEAGENTID uniqueidentifier = '',
                @REGEOCODE bit = 0
            ) as
                set nocount on;

                if exists(select ID from dbo.ADDRESSCOORDINATES where ADDRESSID = @ADDRESSID)
                begin
                    update
                        dbo.ADDRESSCOORDINATES
                    set
                        LATITUDE = @LATITUDE, 
                        LONGITUDE = @LONGITUDE,
                        PENDINGGEOCODE = @PENDINGGEOCODE,
                        INVALIDGEOCODE = @INVALIDGEOCODE,
                        MATCHCODE = coalesce(@MATCHCODE,''),
                        MATCHEDMETHOD = coalesce(@MATCHEDMETHOD,''),
                        GEOCODEEXCEPTION = coalesce(@GEOCODEEXCEPTION,''),
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = getdate(),
                        REGEOCODE = @REGEOCODE
                    where
                        ADDRESSID = @ADDRESSID;
                end
                else
                begin
                    insert into dbo.ADDRESSCOORDINATES (
                        ADDRESSID,
                        LATITUDE,
                        LONGITUDE,
                        PENDINGGEOCODE,
                        INVALIDGEOCODE,
                        MATCHCODE,
                        MATCHEDMETHOD,
                        GEOCODEEXCEPTION,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED,
                        REGEOCODE
                    )
                    values (
                        @ADDRESSID,
                        @LATITUDE,
                        @LONGITUDE,
                        @PENDINGGEOCODE,
                        @INVALIDGEOCODE,
                        coalesce(@MATCHCODE,''),
                        coalesce(@MATCHEDMETHOD,''),
                        coalesce(@GEOCODEEXCEPTION,''),
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        getdate(),
                        getdate(),
                        @REGEOCODE
                    );
                end;