Home
Do you check your "Controlled Lookup Data" (aka Reference Data) is still there with procValidate?
  v2.0 Posted at 20/10/2010 7:57 PM by System Account

Controlled Lookup Data is when data is tightly coupled to the application. If the data is not there, you have problems. So how do we check to see if data is still there?

The simplest way is to add a procValidate (Stored Procedure) to check that all the lookup data is still there.

Figure: procValidates are just like a nagging wife

Let's look at an example, of a combo that is populated with Controlled Lookup data (just 4 records)  
Figure: How do I make sure these 4 records never go missing?
CREATE PROCEDURE procValidate_Region 
AS

    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Eastern')
        PRINT 'Eastern is there'
    ELSE
        RAISERROR(N'Lack of Eastern', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Western')
        PRINT Western is there'
    ELSE
        RAISERROR(N'Lack of Western', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Northern')
        PRINT 'Northern is there'
    ELSE
        RAISERROR(N'Lack of Northern', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Southern')
        PRINT 'Southern is there'
    ELSE
        RAISERROR(N'Lack of Southern', 10, 1)
Figure: Implement a stored procedure to check the 'Controlled Lookup Data' does not go missing
Note: As this procedure will be executed many times, it must be Idempotent

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: