USP_SETSKYACCESSKEY

CREATE procedure dbo.USP_SETSKYACCESSKEY
(
  @SKYACCESSKEY nvarchar(36),
  @CHANGEAGENTID uniqueidentifier
)
as
begin

  exec dbo.USP_GET_KEY_ACCESS;

  begin try

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

    declare @CURRENTDATE datetime = getdate();

    update dbo.SKYACCESS set
      SKYACCESSKEY = EncryptByKey(key_GUID('sym_BBInfinity'), @SKYACCESSKEY),
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID;

    -- If this is the first time this form is saved, create the row.

    if @@ROWCOUNT = 0
    begin
      insert into dbo.SKYACCESS
      (
        SKYACCESSKEY,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        EncryptByKey(key_GUID('sym_BBInfinity'), @SKYACCESSKEY),
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );
    end

    close symmetric key sym_BBInfinity;
  end try
  begin catch
    close symmetric key sym_BBInfinity;
    exec dbo.USP_RAISE_ERROR;
  end catch

end