_article-wffm-stored-procedure.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Add_FormSummary]
  @FormId [uniqueidentifier],
  @FieldId [uniqueidentifier],
  @FieldValueId [bigint],
  @FieldName [nvarchar],
  @Count [int]
AS
BEGIN

SET NOCOUNT ON;

  BEGIN TRY

  MERGE
    [Fact_FormSummary] AS [target]
  USING
    (
      VALUES
      (
        @FormId,
        @FieldId,
        @FieldValueId,
        @FieldName,
        @Count        
      )
    )
    AS [source]
    (
      [FormId],
      [FieldId],
      [FieldValueId],
      [FieldName],
      [Count]
    )
    ON
      ([target].[FormId] = [source].[FormId]) AND
      ([target].[FieldId] = [source].[FieldId]) AND
      ([target].[FieldValueId] = [source].[FieldValueId])
    WHEN MATCHED THEN
      UPDATE
        SET
          [target].[Count] = ([target].[Count] + [source].[Count]),
          [target].[FieldName] = [source].[FieldName]
    WHEN NOT MATCHED THEN
      INSERT
      (
        [FormId],
        [FieldId],
        [FieldValueId],
        [FieldName],
        [Count]
      )
      VALUES
      (
        [source].[FormId],
        [source].[FieldId],
        [source].[FieldValueId],
        [source].[FieldName],
        [source].[Count]
      );

  END TRY
  BEGIN CATCH

    DECLARE @error_number INTEGER = ERROR_NUMBER();
    DECLARE @error_severity INTEGER = ERROR_SEVERITY();
    DECLARE @error_state INTEGER = ERROR_STATE();
    DECLARE @error_message NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @error_procedure SYSNAME = ERROR_PROCEDURE();
    DECLARE @error_line INTEGER = ERROR_LINE();

    IF( @error_number = 2627 )
    BEGIN

      UPDATE
        [dbo].[Fact_FormSummary]
      SET
        [Count] = ([Count] + @Count),
        [FieldName] = @FieldName
      WHERE
        [FormId] = @FormId AND
        [FieldId] = @FieldId AND
        [FieldValueId] = @FieldValueId;

      IF( @@ROWCOUNT != 1 )
      BEGIN
        RAISERROR( 'Failed to insert or update rows in the [Fact_FormSummary] table.', 18, 1 ) WITH NOWAIT;
      END

    END
    ELSE
    BEGIN

      RAISERROR( N'T-SQL ERROR %d, SEVERITY %d, STATE %d, PROCEDURE %s, LINE %d, MESSAGE: %s', @error_severity, 1, @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message ) WITH NOWAIT;

    END;

  END CATCH;

END