USP_WPSOCIALPROFILE_ADD

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@WPSOCIALID	uniqueidentifier	IN
@LOCATION	nvarchar(4000)	IN
@OCCUPATION	nvarchar(2048)	IN
@PROFILEFULLHASH	nvarchar(32)	IN
@COMPANY	nvarchar(4000)	IN
@EMAIL	nvarchar(200)	IN
@SEQUENCE	int	IN
Definition

Copy


CREATE procedure dbo.USP_WPSOCIALPROFILE_ADD (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @WPSOCIALID uniqueidentifier,
        @LOCATION nvarchar(4000) = '',
        @OCCUPATION nvarchar(2048) = '',
        @PROFILEFULLHASH nvarchar(32) = '',
        @COMPANY nvarchar(4000) = '',
        @EMAIL nvarchar(200) = '',
        @SEQUENCE int = 0
            )
as
begin
    -- do work


  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  begin try

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

        declare @ISNEW bit = 0;
        if @LOCATION <> '' or @OCCUPATION <> '' or @COMPANY <> ''
        set @ISNEW = 1;

        --Get any existing ID

        select 
            @ID = ID 
        from 
            dbo.WPSOCIALPROFILE
        where 
            WPSOCIALID=@WPSOCIALID and
      EMAIL=@EMAIL;

        -- Record exists

        if @ID is not null 
        begin

      -- Only update social profile if location, occupation, or company has changed

      if not exists(select top 1 1 from dbo.WPSOCIALPROFILE where @ID = ID and PROFILEFULLHASH = @PROFILEFULLHASH)
      update 
              dbo.WPSOCIALPROFILE
      set
        LOCATION = @LOCATION,
        OCCUPATION = @OCCUPATION,
        COMPANY = @COMPANY,
        PROFILEFULLHASH = @PROFILEFULLHASH,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE,
        ISNEW = @ISNEW
      where
              @ID = ID;

    end else begin

      --Record is new and needs to be Added

            if @ID is null
                set @ID = newid();

      insert into dbo.WPSOCIALPROFILE (
          ID,
        WPSOCIALID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED, 
          DATECHANGED,
        EMAIL,
          LOCATION,
          OCCUPATION,
          COMPANY,
        PROFILEFULLHASH,
        ISNEW
      ) values
      (
          @ID,
        @WPSOCIALID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE, 
          @CURRENTDATE,
          coalesce(@EMAIL, ''),
          coalesce(@LOCATION, ''),
          coalesce(@OCCUPATION, ''),
          coalesce(@COMPANY, ''),
          coalesce(@PROFILEFULLHASH, ''),
        @ISNEW
        )
    end

    if @SEQUENCE = 1
    begin
    -- This profile may populate the social media biography

      if not exists(select top 1 1 from dbo.WPSOCIAL where ID = @WPSOCIALID and PROFILEFULLHASH = @PROFILEFULLHASH )
      begin
        update 
          dbo.WPSOCIAL
        set
            LOCATION = coalesce(nullif(LOCATION, ''),@LOCATION),
            OCCUPATION = coalesce(nullif(OCCUPATION, ''),@OCCUPATION),
            COMPANY = coalesce(nullif(COMPANY, ''),@COMPANY),
          PROFILEFULLHASH = @PROFILEFULLHASH,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
          ID = @WPSOCIALID
      end
    end
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
  end catch

end