Carter / Repair-GrowthPlansDemographicValues.sql

-- ========== CONFIGURATION VARIABLES ========== DECLARE @CompanyId INT = 2271; -- Analytics CompanyId DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity'; -- ========== INTERNAL VARIABLES (Auto-populated) ========== DECLARE @CompanyDemographicId INT; DECLARE @CompanyDemographicValueId_NotStarted INT; DECLARE @CompanyDemographicValueId_Draft INT; DECLARE @CompanyDemographicValueId_Shared INT; DECLARE @CompanyDemographicValueMappingId_Draft INT; DECLARE @CompanyDemographicValueMappingId_Shared INT; BEGIN TRANSACTION; BEGIN TRY -- ========== Locate the demographic ========== SELECT @CompanyDemographicId = cd.Id FROM Analytics.survey.CompanyDemographic cd INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id WHERE cd.CompanyId = @CompanyId AND cdnm.Text = @DemographicName; IF @CompanyDemographicId IS NULL BEGIN RAISERROR('No CompanyDemographic found for CompanyId=%d, Name=''%s''.', 16, 1, @CompanyId, @DemographicName); END -- ========== Read current mapping/value IDs ========== SELECT @CompanyDemographicValueId_NotStarted = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Not Started'; SELECT @CompanyDemographicValueMappingId_Draft = cdvm.Id, @CompanyDemographicValueId_Draft = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Draft'; SELECT @CompanyDemographicValueMappingId_Shared = cdvm.Id, @CompanyDemographicValueId_Shared = cdvm.CompanyDemographicValueId FROM Analytics.survey.CompanyDemographicValueMapping cdvm JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId WHERE cdv.CompanyDemographicId = @CompanyDemographicId AND cdvm.Text = 'Shared'; PRINT 'Current state:'; PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR); PRINT ' CompanyDemographicValueId "Not Started": ' + ISNULL(CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR), 'NULL'); PRINT ' CompanyDemographicValueId "Draft": ' + ISNULL(CAST(@CompanyDemographicValueId_Draft AS VARCHAR), 'NULL'); PRINT ' CompanyDemographicValueId "Shared": ' + ISNULL(CAST(@CompanyDemographicValueId_Shared AS VARCHAR), 'NULL'); -- ========== Verify the malformed state before touching anything ========== IF @CompanyDemographicValueId_NotStarted IS NULL OR @CompanyDemographicValueId_Draft IS NULL OR @CompanyDemographicValueId_Shared IS NULL BEGIN RAISERROR('One or more expected mappings (Not Started, Draft, Shared) were not found.', 16, 1); END IF @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Draft AND @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Shared AND @CompanyDemographicValueId_Draft <> @CompanyDemographicValueId_Shared BEGIN RAISERROR('Structure is already correct: all three mappings have distinct CompanyDemographicValueIds. No repair needed.', 16, 1); END -- ========== Repair ========== IF @CompanyDemographicValueId_Draft = @CompanyDemographicValueId_NotStarted BEGIN INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY(); UPDATE Analytics.survey.CompanyDemographicValueMapping SET CompanyDemographicValueId = @CompanyDemographicValueId_Draft WHERE Id = @CompanyDemographicValueMappingId_Draft; PRINT 'Repaired "Draft": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR); END IF @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_NotStarted OR @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_Draft BEGIN INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) VALUES (@CompanyDemographicId); SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY(); UPDATE Analytics.survey.CompanyDemographicValueMapping SET CompanyDemographicValueId = @CompanyDemographicValueId_Shared WHERE Id = @CompanyDemographicValueMappingId_Shared; PRINT 'Repaired "Shared": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR); END COMMIT TRANSACTION; PRINT ''; PRINT '========== SUCCESS: Repair completed successfully =========='; PRINT ' CompanyDemographicValueId "Not Started": ' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR); PRINT ' CompanyDemographicValueId "Draft": ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR); PRINT ' CompanyDemographicValueId "Shared": ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; PRINT ''; PRINT '========== ERROR: Transaction rolled back =========='; PRINT 'Error Message: ' + ERROR_MESSAGE(); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); THROW; END CATCH;