USP_COUNTRYADDRESSFORMAT_ADDORUPDATE

Adds or updates a row in COUNTRYADDRESSFORMAT by format name and format function.
Parameters

Parameter	Parameter Type	Mode
@FORMATNAME	nvarchar(50)	IN
@FORMATSQLFUNCTION	nvarchar(200)	IN
@CHANGEAGENTID	uniqueidentifier	IN
Definition

Copy


            CREATE procedure dbo.USP_COUNTRYADDRESSFORMAT_ADDORUPDATE
            (
                @FORMATNAME nvarchar(50),
                @FORMATSQLFUNCTION nvarchar(200),
                @CHANGEAGENTID uniqueidentifier = null
            )
            as begin

                set nocount on;

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate();

                begin try
                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    declare @FORMATID uniqueidentifier
                    select @FORMATID = ID
                    from COUNTRYADDRESSFORMAT
                    where FORMATSQLFUNCTION = @FORMATSQLFUNCTION;

                    if @FORMATID is null
                    begin
                        insert into COUNTRYADDRESSFORMAT
                        (
                            ID,
                            FORMATNAME,
                            FORMATSQLFUNCTION,
                            SEQUENCE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values
                        (
                            newid(),
                            @FORMATNAME,
                            @FORMATSQLFUNCTION,
                            coalesce((select max(SEQUENCE) from COUNTRYADDRESSFORMAT) + 1,0), 
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        );
                    end
                    else
                    begin
                        update COUNTRYADDRESSFORMAT
                        set
                            FORMATNAME = @FORMATNAME,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @FORMATID;
                    end
                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;
            end