title: How to build a rules table in sql server
body: <p>There are quite a few business rules which are currently hardcoded within a stored procedure. Wanted to explore the option of setting up a rules table where in we intend to key-in all business rules and based on it execute the stored procedure. </p>
  
  <p>Though the system is little complicated have provided a simple version here. </p>
  
  <pre><code>Create table tblTest
  (
      TranID int primary key not null,
      FName varchar(20) not null,
      Age int not null,
      Salary money not null,
      MaritalStatus char(1) not null
  )
  
  Insert into tblTest values (1, 'Alex', 26, '25000.00','Y')
  Insert into tblTest values (2, 'Brenda', 25, '14500.00','Y')
  Insert into tblTest values (3, 'Peter', 69, '50000.00','N')
  Insert into tblTest values (4, 'Paul', 64, '74500.00','Y')
  </code></pre>
  
  <p>Now to keep the example simple lets assume the business rules to be the following:
  1. Age >=25,<br>
  2. Age &lt; 65 and<br>
  3. Salary > 15K</p>
  
  <pre><code>Create table tblBusRule
  (
      RuleID int Primary key not null,
      ColName varchar(20) not null,
      Operator varchar(2) not null,
      ColValue varchar(10) not null,
      RuleOrder int not null
  )
  
  Insert into tblBusRule values (1, 'Age', '&gt;=', '25', 1)
  Insert into tblBusRule values (2, 'Age', '&lt;', '65', 2)
  Insert into tblBusRule values (3, 'Salary', '&gt;', '15000.00', 3)
  </code></pre>
  
  <p>The direct query would be something like this which would output the record 1 (Alex) and 4 (Paul) alone.</p>
  
  <pre><code>Select * from tblTest
  where 
          age &gt;=25 and 
          age &lt; 65 and 
          salary &gt; '15000.00'
  </code></pre>
  
  <p>Now how to make this dynamic based on the rules mentioned in tblBusRule? </p>