text: ﻿/*************************************************************
      Stored procedures - UpsertSearchParams
  **************************************************************/
  --
  -- STORED PROCEDURE
  --     UpsertSearchParams
  --
  -- DESCRIPTION
  --     Given a set of search parameters, creates or updates the parameters.
  --
  -- PARAMETERS
  --     @searchParams
  --         * The updated existing search parameters or the new search parameters
  --
  -- RETURN VALUE
  --     The IDs and URIs of the search parameters that were inserted (not updated).
  --
  CREATE PROCEDURE dbo.UpsertSearchParams
      @searchParams dbo.SearchParamTableType_1 READONLY
  AS
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  BEGIN TRANSACTION;
  DECLARE @lastUpdated AS DATETIMEOFFSET (7) = SYSDATETIMEOFFSET();
  DECLARE @summaryOfChanges TABLE (
      Uri    VARCHAR (128) COLLATE Latin1_General_100_CS_AS NOT NULL,
      Action VARCHAR (20)  NOT NULL);
  
  -- Acquire and hold an exclusive table lock for the entire transaction to prevent parameters from being added or modified during upsert.
  MERGE INTO dbo.SearchParam WITH (TABLOCKX)
   AS target
  USING @searchParams AS source ON target.Uri = source.Uri
  WHEN MATCHED THEN UPDATE 
  SET Status               = source.Status,
      LastUpdated          = @lastUpdated,
      IsPartiallySupported = source.IsPartiallySupported
  WHEN NOT MATCHED BY TARGET THEN INSERT (Uri, Status, LastUpdated, IsPartiallySupported) VALUES (source.Uri, source.Status, @lastUpdated, source.IsPartiallySupported)
  OUTPUT source.Uri, $ACTION INTO @summaryOfChanges;
  SELECT SearchParamId,
         SearchParam.Uri
  FROM   dbo.SearchParam AS searchParam
         INNER JOIN
         @summaryOfChanges AS upsertedSearchParam
         ON searchParam.Uri = upsertedSearchParam.Uri
  WHERE  upsertedSearchParam.Action = 'INSERT';
  COMMIT TRANSACTION;
  GO
  