Using a table for multiple query parameters

Question

I'm trying to clean up a huge existing SQL script that's made up of several SQL queries that each get a number of records from one table and appends them to an "output table". It seems to me it would be so much easier to create one query to get all the records at once. The trouble is, each of these existing queries uses all kinds of criteria in their WHERE clauses.

I wonder if I could simply create a table containing all the WHERE criteria and then write some sort of SQL statement that uses all those criteria. Then if the criteria ever has to be changed I could just edit or add to that table, and not have to go through and rewrite the SQL.

For example, right now it's something like:

INSERT INTO SelectedCustomers (CustomerID)
SELECT CustomerID FROM Customers WHERE CustomerName LIKE '%Jones%' AND CustomerAge > '18'

Then further along there's:

INSERT INTO SelectedCustomers (CustomerID)
SELECT CustomerID FROM Customers WHERE CustomerState = 'CT' OR CustomerHair = 'Brown'

and further down is:

INSERT INTO SelectedCustomers (CustomerID)
SELECT CustomerID FROM Customers WHERE CustomerState = 'NY' AND CustomerHair <> 'Red'

etc (it goes on and on...)

Any ideas, links, etc?

So do you want to combine these into one statement or do you want to have the criteria in "another table" Or both? — Hogan, Jan 10 '17 at 16:01
How you are planning to store the conditions in table. It requires `Dynamic sql`. You need to show how conditions are stored in table — Pரதீப், Jan 10 '17 at 16:04

score 0 · Answer 1 · answered Jan 10 '17 at 16:09

0

There are a couple of ways to do this -- if the criteria is always the same (eg LIKE '%Jones%' or CustomerAge > '18') then you are talking about an inner join.

Since your criteria seems to vary a lot I would not suggest that (it can be done with dynamic sql but in my opinion this will just make your system harder to maintain and slower.)

Instead what I would suggest since your example only shows selection based on IDs from one table a view and then a join for the insert table based on that view. When your criteria changes change the view. That would look like this.

  CREATE OR REPLACE VIEW INSERT_CRITERIA (CustomerID) AS

    SELECT CustomerID FROM Customers WHERE CustomerName LIKE '%Jones%' AND CustomerAge > '18'
      UNION 
    SELECT CustomerID FROM Customers WHERE CustomerState = 'CT' OR CustomerHair = 'Brown'
      UNION 
    SELECT CustomerID FROM Customers WHERE CustomerState = 'NY' AND CustomerHair <> 'Red'

Then the insert is just

INSERT INTO SelectedCustomers (CustomerID)
   SELECT CustomerID FROM INSERT_CRITERIA

The advantage here is that you remove your business rules (eg CustomerState = 'CT') to an easy to manage and monitor location while still getting the advantage of the compiler being able to optimize those functions.

If you really want to automate it I will still suggest this view -- just have the view generated from the tables that store the business rules. Creating / updating the view would be easy to script.

answered Jan 10 '17 at 16:09

Hogan

69,564
10
76
117

That just seems the same as writing: INSERT INTO SelectedCustomers (CustomerID) ( SELECT CustomerID FROM Customers WHERE CustomerName LIKE '%Jones%' AND CustomerAge > '18' UNION SELECT CustomerID FROM Customers WHERE CustomerState = 'CT' OR CustomerHair = 'Brown' UNION SELECT CustomerID FROM Customers WHERE CustomerState = 'NY' AND CustomerHair <> 'Red' ) but what I was sort of hoping to do is use a TABLE for all the values in the WHERE clause(s) so that we don't have to edit the SQL itself at all anymore, just change the values in some table. – Brian Battles Jan 10 '17 at 16:22
@BrianBattles -- It isn't. But by creating a *separation of concerns* you have now enhanced your design. This is fundamental in modular programing. For example it is now possible to script the creation of the view from a table. Something that would be much harder if the logic in the view was still contained in the stored procedure. – Hogan Jan 10 '17 at 16:24
Well, Hogan, the design of this query (and the whole database) is a disaster, that was created a few years ago before I got to this company. I'm trying to untangle it and fix it! :) – Brian Battles Jan 10 '17 at 16:26
@BrianBattles -- and I've been in the same situation many times -- that is why my suggestion might be helpful and based on experience. I'm not interested in arguing with you about if it is good or correct -- I already know the answer to that question. You are welcome to use it or not as you wish but I'm not interested in any more "discussion". – Hogan Jan 10 '17 at 16:29
@BrianBattles -- Creating a whole new dynamic system for storing "parameters" is almost certainly not a good way to simplify a legacy system. Creating separation of concerns and making "modules" is. Views are one the best tools for doing so in a DBA's arsenal – Hogan Jan 10 '17 at 16:34

Using a table for multiple query parameters

1 Answers1