Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment