Carter 已修改 . 還原成這個修訂版本
1 file changed, 118 insertions
Repair-GrowthPlansDemographicValues.sql(檔案已創建)
| @@ -0,0 +1,118 @@ | |||
| 1 | + | -- ========== CONFIGURATION VARIABLES ========== | |
| 2 | + | DECLARE @CompanyId INT = 2271; -- Analytics CompanyId | |
| 3 | + | DECLARE @DemographicName NVARCHAR(100) = 'Growth Plan Activity'; | |
| 4 | + | ||
| 5 | + | -- ========== INTERNAL VARIABLES (Auto-populated) ========== | |
| 6 | + | DECLARE @CompanyDemographicId INT; | |
| 7 | + | DECLARE @CompanyDemographicValueId_NotStarted INT; | |
| 8 | + | DECLARE @CompanyDemographicValueId_Draft INT; | |
| 9 | + | DECLARE @CompanyDemographicValueId_Shared INT; | |
| 10 | + | DECLARE @CompanyDemographicValueMappingId_Draft INT; | |
| 11 | + | DECLARE @CompanyDemographicValueMappingId_Shared INT; | |
| 12 | + | ||
| 13 | + | BEGIN TRANSACTION; | |
| 14 | + | BEGIN TRY | |
| 15 | + | ||
| 16 | + | -- ========== Locate the demographic ========== | |
| 17 | + | SELECT @CompanyDemographicId = cd.Id | |
| 18 | + | FROM Analytics.survey.CompanyDemographic cd | |
| 19 | + | INNER JOIN Analytics.survey.CompanyDemographicNameMapping cdnm ON cdnm.CompanyDemographicId = cd.Id | |
| 20 | + | WHERE cd.CompanyId = @CompanyId | |
| 21 | + | AND cdnm.Text = @DemographicName; | |
| 22 | + | ||
| 23 | + | IF @CompanyDemographicId IS NULL | |
| 24 | + | BEGIN | |
| 25 | + | RAISERROR('No CompanyDemographic found for CompanyId=%d, Name=''%s''.', 16, 1, @CompanyId, @DemographicName); | |
| 26 | + | END | |
| 27 | + | ||
| 28 | + | -- ========== Read current mapping/value IDs ========== | |
| 29 | + | SELECT @CompanyDemographicValueId_NotStarted = cdvm.CompanyDemographicValueId | |
| 30 | + | FROM Analytics.survey.CompanyDemographicValueMapping cdvm | |
| 31 | + | JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId | |
| 32 | + | WHERE cdv.CompanyDemographicId = @CompanyDemographicId | |
| 33 | + | AND cdvm.Text = 'Not Started'; | |
| 34 | + | ||
| 35 | + | SELECT @CompanyDemographicValueMappingId_Draft = cdvm.Id, | |
| 36 | + | @CompanyDemographicValueId_Draft = cdvm.CompanyDemographicValueId | |
| 37 | + | FROM Analytics.survey.CompanyDemographicValueMapping cdvm | |
| 38 | + | JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId | |
| 39 | + | WHERE cdv.CompanyDemographicId = @CompanyDemographicId | |
| 40 | + | AND cdvm.Text = 'Draft'; | |
| 41 | + | ||
| 42 | + | SELECT @CompanyDemographicValueMappingId_Shared = cdvm.Id, | |
| 43 | + | @CompanyDemographicValueId_Shared = cdvm.CompanyDemographicValueId | |
| 44 | + | FROM Analytics.survey.CompanyDemographicValueMapping cdvm | |
| 45 | + | JOIN Analytics.survey.CompanyDemographicValue cdv ON cdv.Id = cdvm.CompanyDemographicValueId | |
| 46 | + | WHERE cdv.CompanyDemographicId = @CompanyDemographicId | |
| 47 | + | AND cdvm.Text = 'Shared'; | |
| 48 | + | ||
| 49 | + | PRINT 'Current state:'; | |
| 50 | + | PRINT ' CompanyDemographicId: ' + CAST(@CompanyDemographicId AS VARCHAR); | |
| 51 | + | PRINT ' CompanyDemographicValueId "Not Started": ' + ISNULL(CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR), 'NULL'); | |
| 52 | + | PRINT ' CompanyDemographicValueId "Draft": ' + ISNULL(CAST(@CompanyDemographicValueId_Draft AS VARCHAR), 'NULL'); | |
| 53 | + | PRINT ' CompanyDemographicValueId "Shared": ' + ISNULL(CAST(@CompanyDemographicValueId_Shared AS VARCHAR), 'NULL'); | |
| 54 | + | ||
| 55 | + | -- ========== Verify the malformed state before touching anything ========== | |
| 56 | + | IF @CompanyDemographicValueId_NotStarted IS NULL | |
| 57 | + | OR @CompanyDemographicValueId_Draft IS NULL | |
| 58 | + | OR @CompanyDemographicValueId_Shared IS NULL | |
| 59 | + | BEGIN | |
| 60 | + | RAISERROR('One or more expected mappings (Not Started, Draft, Shared) were not found.', 16, 1); | |
| 61 | + | END | |
| 62 | + | ||
| 63 | + | IF @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Draft | |
| 64 | + | AND @CompanyDemographicValueId_NotStarted <> @CompanyDemographicValueId_Shared | |
| 65 | + | AND @CompanyDemographicValueId_Draft <> @CompanyDemographicValueId_Shared | |
| 66 | + | BEGIN | |
| 67 | + | RAISERROR('Structure is already correct: all three mappings have distinct CompanyDemographicValueIds. No repair needed.', 16, 1); | |
| 68 | + | END | |
| 69 | + | ||
| 70 | + | -- ========== Repair ========== | |
| 71 | + | IF @CompanyDemographicValueId_Draft = @CompanyDemographicValueId_NotStarted | |
| 72 | + | BEGIN | |
| 73 | + | INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) | |
| 74 | + | VALUES (@CompanyDemographicId); | |
| 75 | + | SET @CompanyDemographicValueId_Draft = SCOPE_IDENTITY(); | |
| 76 | + | ||
| 77 | + | UPDATE Analytics.survey.CompanyDemographicValueMapping | |
| 78 | + | SET CompanyDemographicValueId = @CompanyDemographicValueId_Draft | |
| 79 | + | WHERE Id = @CompanyDemographicValueMappingId_Draft; | |
| 80 | + | ||
| 81 | + | PRINT 'Repaired "Draft": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR); | |
| 82 | + | END | |
| 83 | + | ||
| 84 | + | IF @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_NotStarted | |
| 85 | + | OR @CompanyDemographicValueId_Shared = @CompanyDemographicValueId_Draft | |
| 86 | + | BEGIN | |
| 87 | + | INSERT INTO Analytics.survey.CompanyDemographicValue (CompanyDemographicId) | |
| 88 | + | VALUES (@CompanyDemographicId); | |
| 89 | + | SET @CompanyDemographicValueId_Shared = SCOPE_IDENTITY(); | |
| 90 | + | ||
| 91 | + | UPDATE Analytics.survey.CompanyDemographicValueMapping | |
| 92 | + | SET CompanyDemographicValueId = @CompanyDemographicValueId_Shared | |
| 93 | + | WHERE Id = @CompanyDemographicValueMappingId_Shared; | |
| 94 | + | ||
| 95 | + | PRINT 'Repaired "Shared": reassigned to new CompanyDemographicValueId ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR); | |
| 96 | + | END | |
| 97 | + | ||
| 98 | + | COMMIT TRANSACTION; | |
| 99 | + | PRINT ''; | |
| 100 | + | PRINT '========== SUCCESS: Repair completed successfully =========='; | |
| 101 | + | PRINT ' CompanyDemographicValueId "Not Started": ' + CAST(@CompanyDemographicValueId_NotStarted AS VARCHAR); | |
| 102 | + | PRINT ' CompanyDemographicValueId "Draft": ' + CAST(@CompanyDemographicValueId_Draft AS VARCHAR); | |
| 103 | + | PRINT ' CompanyDemographicValueId "Shared": ' + CAST(@CompanyDemographicValueId_Shared AS VARCHAR); | |
| 104 | + | ||
| 105 | + | END TRY | |
| 106 | + | BEGIN CATCH | |
| 107 | + | IF @@TRANCOUNT > 0 | |
| 108 | + | ROLLBACK TRANSACTION; | |
| 109 | + | ||
| 110 | + | PRINT ''; | |
| 111 | + | PRINT '========== ERROR: Transaction rolled back =========='; | |
| 112 | + | PRINT 'Error Message: ' + ERROR_MESSAGE(); | |
| 113 | + | PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); | |
| 114 | + | PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); | |
| 115 | + | PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); | |
| 116 | + | ||
| 117 | + | THROW; | |
| 118 | + | END CATCH; | |
上一頁
下一頁