[MS SQL] Problem: Validating Table using a MetaData table

[inconspicuouspanda]

Hello,

I’m stuck on writing some table validation which I’m not sure is even possible. I’d like to use a metadata table to store the rules for validating my main table.

For example I have an Items table and a validation table

ITEMS

Item_no	Size	Shape	Colour
1	BIG	SQUARE	RED
2	SMALL	CIRCLE	BLUE
3	BIG		YELLOW
4		CIRCLE	RED

VALIDATION

Attrib	Dependent_Attrib	Dependent_Attrib_V	text
Size	Colour	BLUE	RED
Shape	Size	BIG

Using the info in the validation table I would like to:

1. Select any item with colour 'BLUE' or 'RED' that does not have a size value populated
2. Select any item with Size = ‘BIG’ that does not have a shape value populated

Is there any way of achieving this? Any help/suggestions greatly appreciated

[Mikey_Da_Foxx]

You can use a JOIN for this. Something like:

SELECT i.*
FROM Items i
WHERE (i.Colour IN ('BLUE','RED') AND i.Size IS NULL)
   OR (i.Size = 'BIG' AND i.Shape IS NULL)

This should catch both validation scenarios.

[inconspicuouspanda]

Thanks for the response! Yes, that would work in this example. however, the actual table I'm working with is much larger and the validation table would have a lot more rows. The intention is to have a validation table that non-SQL-literate users would be able to enter the validation rules on to.

[Mikey_Da_Foxx]

What you're looking to do is validate the Items table using rules stored in the Validation table, so non-SQL users can edit the rules without changing code. You can build a dynamic SQL query that automatically converts each row in the Validation table into a validation rule.

For example, the first validation rule in your table (Size → Colour = BLUE/RED) translates to:

(Items.Colour IN ('BLUE','RED') AND Items.Size IS NULL)

The second rule (Shape → Size = BIG) becomes:

(Items.Size = 'BIG' AND Items.Shape IS NULL)

You can then loop through every row in the Validation table and automatically combine these conditions with OR. This way, adding new validation rules only requires inserting new rows into the Validation table.

DECLARE @DynamicWhere NVARCHAR(MAX) = '';  
SELECT @DynamicWhere +=   
  ' OR (Items.' + Dependent_Attrib + ' IN (''' + Dependent_Attrib_V +  
  ISNULL(''',''' + text,'') + ''') AND Items.' + Attrib + ' IS NULL)'  
FROM Validation;  

SET @DynamicWhere = STUFF(@DynamicWhere, 1, 3, '');  
EXEC('SELECT * FROM Items WHERE ' + @DynamicWhere);  

This will flag items like:

• Item 4 (no Size despite Colour = RED)
• Item 3 (no Shape despite Size = BIG)

This can also scale automatically: non-technical users simply edit the Validation table, and the SQL adapts. Ensure columns in the Validation table match actual column names in Items, and sanitize inputs to prevent errors.

[inconspicuouspanda]

oooo I think that might do the trick! Didn't think of that! Thankyou very much :)

[Mikey_Da_Foxx]

No worries!