USP_ADDUPDATE_LINKEDCMSUSERSOCIALMEDIAACCOUNT

CREATE procedure dbo.USP_ADDUPDATE_LINKEDCMSUSERSOCIALMEDIAACCOUNT
(
    @CONSTITUENTSEQUENCEID int,
    @SOCIALMEDIASERVICEID uniqueidentifier,
    @USERID nvarchar(100),
    @URL nvarchar(2047)
)
as
begin

    declare @INFOSOURCECODEID uniqueidentifier
    select @INFOSOURCECODEID = [INFOSOURCECODEID] from [NETCOMMUNITYDEFAULTCODEMAP]

    declare @CHANGEAGENTID uniqueidentifier;
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

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

    declare @CONSTITUENTID uniqueidentifier
    select @CONSTITUENTID = ID from dbo.CONSTITUENT where SEQUENCEID = @CONSTITUENTSEQUENCEID

    if @INFOSOURCECODEID is not null
    begin
        merge into dbo.SOCIALMEDIAACCOUNT as TARGET
        using (SELECT @CONSTITUENTID as CONSTITUENTID,  @SOCIALMEDIASERVICEID as SOCIALMEDIASERVICEID, @INFOSOURCECODEID as INFOSOURCECODEID) as SOURCE
        on TARGET.CONSTITUENTID = SOURCE.CONSTITUENTID and TARGET.SOCIALMEDIASERVICEID = SOURCE.SOCIALMEDIASERVICEID and TARGET.INFOSOURCECODEID = SOURCE.INFOSOURCECODEID
        when matched then
          update set USERID = @USERID, URL = @URL
        when not matched by target then
          insert (CONSTITUENTID, SOCIALMEDIASERVICEID, USERID, URL, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
          values (@CONSTITUENTID, @SOCIALMEDIASERVICEID, @USERID, @URL, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
    end
    else
    begin
        merge into dbo.SOCIALMEDIAACCOUNT as TARGET
        using (SELECT @CONSTITUENTID as CONSTITUENTID,  @SOCIALMEDIASERVICEID as SOCIALMEDIASERVICEID, @INFOSOURCECODEID as INFOSOURCECODEID) as SOURCE
        on TARGET.CONSTITUENTID = SOURCE.CONSTITUENTID and TARGET.SOCIALMEDIASERVICEID = SOURCE.SOCIALMEDIASERVICEID and TARGET.INFOSOURCECODEID is null and TARGET.URL = @URL and TARGET.USERID = @USERID
        when not matched by target then
          insert (CONSTITUENTID, SOCIALMEDIASERVICEID, USERID, URL, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) 
          values (@CONSTITUENTID, @SOCIALMEDIASERVICEID, @USERID, @URL, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);      
    end
end