By the way you're describing this - I'd recommend NOT making this a pure SQL approach. I certainly see storing the data there, and having the consequences of tier assignment set up there, but I'd recommend NOT putting the dynamic rules (as you call them) into TSQL. TSQL isn't particularly well suited for what you plan on doing.

Your best bet might be to look at an open-source rules engine, allowing you to use/design a spredsheet-style rule editor which automatically updates the rule assignment without needing ugly and complicated dynamic SQL stored procedures to do the same.

There are lots of open source ones out there. We've landed on DROOLS for now, but we've had limited success with InRULES and NxBRE previously.