Do you check your "Controlled Lookup Data" (aka Reference Data) is still there?

Last updated by Brady Stroud [SSW] about 1 month ago.See history

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?

Let's look at an example, of a combo that is populated with Controlled Lookup data (just 4 records)

Modern Frameworks (EF)

With Frameworks like Entity Framework you can write unit tests to catch data issues before it becomes an problem.

Legacy Applications

With legacy applications, creating a stored procedure will have the same effect with a bit more effort.

TimeProDropDown 1710232021933
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

Adam Cogan
Matt Wicks
Jack Pettit
We open source. Powered by GitHub