MS SQL Stored Procedure with TRY CATCH and UPSERT

mssql_trycatch_upsert.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_application_upsert]
	@Id						int = null,
	@Name					nvarchar(255) = null,
	@Description			nvarchar(255) = null,
	@OperationalStatusCode	nvarchar(255) = null,
	@OperationalStatusDesc	nvarchar(255) = null,
	@CloudOperation			nvarchar(255) = null,
	@SyncStatus				nvarchar(255) = null,
	@ErrorCode				nvarchar(255) = null,
	@ErrorDesc				nvarchar(255) = null,
	@LastUpdated			datetime = null
AS
BEGIN
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	declare @ErrorMessage	nvarchar(255)
	declare @ErrorSeverity	nvarchar(255)
	declare @ErrorState		nvarchar(255)

	
	-- Check if this is an update via ID - UPSERT
	if @Id is not null
	begin
		print 'update via id'
		begin try
			update Application
				set Name = ISNULL(@Name, Name),
				Description = ISNULL(@Description, Description),
				OperationalStatusCode = ISNULL(@OperationalStatusCode, OperationalStatusCode),
				OperationalStatusDesc = ISNULL(@OperationalStatusDesc, OperationalStatusDesc),
				CloudOperation = ISNULL(@CloudOperation, CloudOperation),
				SyncStatus = ISNULL(@SyncStatus, SyncStatus),
				ErrorCode = ISNULL(@ErrorCode, ErrorCode),
				ErrorDesc = ISNULL(@ErrorDesc, ErrorDesc),
				LastUpdated = ISNULL(@LastUpdated, LastUpdated)
			where
				id = @Id
		end try
		begin catch
		    SET @ErrorMessage  = ERROR_MESSAGE()
			SET @ErrorSeverity = ERROR_SEVERITY()
			SET @ErrorState    = ERROR_STATE()
			RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
		end catch
	end


	-- Check if this is an update via NAME - UPSERT
	if @id is null and exists(select Id from Application where Name = @Name)
	begin
		print 'update via name'
		begin try
			update Application
				set Name = ISNULL(@Name, Name),
				Description = ISNULL(@Description, Description),
				OperationalStatusCode = ISNULL(@OperationalStatusCode, OperationalStatusCode),
				OperationalStatusDesc = ISNULL(@OperationalStatusDesc, OperationalStatusDesc),
				CloudOperation = ISNULL(@CloudOperation, CloudOperation),
				SyncStatus = ISNULL(@SyncStatus, SyncStatus),
				ErrorCode = ISNULL(@ErrorCode, ErrorCode),
				ErrorDesc = ISNULL(@ErrorDesc, ErrorDesc),
				LastUpdated = ISNULL(@LastUpdated, LastUpdated)
			where
				Name = @Name
		end try
		begin catch
		    SET @ErrorMessage  = ERROR_MESSAGE()
			SET @ErrorSeverity = ERROR_SEVERITY()
			SET @ErrorState    = ERROR_STATE()
			RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
		end catch
	end


	-- Insert
	if @Id is null and not exists(select Id from Application where Name = @Name)
	begin
		print 'insert'
		begin try
			insert into Application
				(Name, Description, OperationalStatusCode, OperationalStatusDesc, CloudOperation, SyncStatus, LastUpdated)
			values
				(@Name, @Description, @OperationalStatusCode, @OperationalStatusDesc, @CloudOperation, @SyncStatus, @LastUpdated)
		end try
		begin catch
		    SET @ErrorMessage  = ERROR_MESSAGE()
			SET @ErrorSeverity = ERROR_SEVERITY()
			SET @ErrorState    = ERROR_STATE()
			RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
		end catch
	end
END