When it is time to create reports based on your data, the consistency of entity data becomes crucial. It is easy to miss or report incorrectly sales if you strictly base your aggregates on the data coming from a system that has free form data entry on these key fields. 

New Jersey, N.J, NJ, NJ., N J

All these entries will make it for shipping but not for analytical reporting.  

Using TGPValidate to add a business rule to the Customer Maintenance window (Or anywhere in the system that accepts the State field) will assist us in controlling what goes into the system to facilitate reporting and other activities. 

The included script, will prevent users from entering values to the state field not included in the standard abbreviation: 

-- ================================================================
-- Author:       TitaniumGP
-- Created date: 2/17/2020
-- Description:  TitaniumGP Validate for Customer Maintenance window 
-- ================================================================
ALTER PROCEDURE [dbo].[Tgpval_rm_customer_maintenance] @vp_CustomerNumber
VARCHAR(100) = NULL,
                                                       @vp_City
VARCHAR(100) = NULL,
                                                       @vp_State
VARCHAR(100) = NULL,
                                                       @vp_Zip
VARCHAR(100) = NULL,
                                                       @vp_CountryCode
VARCHAR(100) = NULL,
                                                       @vp_Country
VARCHAR(100) = NULL,
                                                       @vp_ErrorNum       INT =
0 output,
                                                       @vp_ErrorMessage
NVARCHAR(4000) =  output,
                                                       @vp_Message
NVARCHAR(4000) =  output
AS
  BEGIN try
      SET nocount ON

      SELECT @vp_ErrorNum = 0,
             @vp_ErrorMessage = ,
             @vp_Message = 

      IF @vp_CountryCode = ‘US’
         AND Rtrim(@vp_State) <> 
         AND @vp_State NOT IN ( ‘AK’, ‘AL’, ‘AR’, ‘AZ’,
                                ‘CA’, ‘CO’, ‘CT’, ‘DE’,
                                ‘FL’, ‘GA’, ‘HI’, ‘IA’,
                                ‘ID’, ‘IL’, ‘IN’, ‘KS’,
                                ‘KY’, ‘LA’, ‘MA’, ‘MD’,
                                ‘ME’, ‘MI’, ‘MN’, ‘MO’,
                                ‘MS’, ‘MT’, ‘NC’, ‘ND’,
                                ‘NE’, ‘NH’, ‘NJ’, ‘NM’,
                                ‘NV’, ‘NY’, ‘OH’, ‘OK’,
                                ‘OR’, ‘PA’, ‘RI’, ‘SC’,
                                ‘SD’, ‘TN’, ‘TX’, ‘UT’,
                                ‘VA’, ‘VT’, ‘WA’, ‘WI’,
                                ‘WV’, ‘WY’ )
        BEGIN
            SELECT @vp_ErrorNum = 250

            SELECT @vp_ErrorMessage = ‘Invalid State Abreviation, please review’

            GOTO exitwitherror
        END

      GOTO normalexit
  END try

  BEGIN catch
      DECLARE @vp_ErrorSeverity  INT,
              @vp_ErrorState     INT,
              @vp_ErrorProcedure NVARCHAR(128),
              @vp_ErrorLine      INT

      SELECT @vp_ErrorNum = Error_number(),
             @vp_ErrorSeverity = Error_severity(),
             @vp_ErrorState = Error_state(),
             @vp_ErrorProcedure = Error_procedure(),
             @vp_ErrorLine = Error_line(),
             @vp_ErrorMessage = Error_message()

      GOTO exitwitherror
  END catch

    NORMALEXIT:

    GOTO endsp

    EXITWITHERROR:

    SELECT @vp_ErrorMessage = @vp_ErrorMessage

    ENDSP:

    RETURN ( @vp_ErrorNum )