Build SQL Query from User Input

Question

I'm trying to build a query in such a way that my coworkers can modify how different cost components are calculated. A simplified version of the query is below:

SELECT 
    ProductWeight * ShippingCost As Shipping,
    ExpectedRevenue * GRTRate * As GRT
FROM PriceTable

I want to allow the user to control how Shipping and GRT calculate, so I created a second table of formulas:

State | Component | Formula
______________________________________________
NJ    | Shipping  | 'ProductWeight * ShippingCost'
NY    | GRT       | 'ExpectedRevenue * GRTRate'

The users are able to modify these formulas through a form in an Access front end. Ideally, I'd like to join PriceTable and Formulas on Product and State and evaluate the formula, but I haven't found any way to make that work, and AFAIK SQL doesn't really work like that.

The current workaround I have:

Based on some answers I've seen here about building dynamic queries, I'm using the XML Path method to create a list of Formulas, like so:

Declare @formulas As nvarchar(max)
Set @formulas = (
    SELECT DISTINCT Formula + ' As ' + Component + ',' As [text()]
    FROM Formulas
    For XML Path('')
)
Set @formulas = LEFT(@formulas, len(@Formulas)-1)

Then, I created a variable that builds the query using the formulas:

Declare @query As nvarchar(max)
@query = 'SELECT ' + @formulas + ' FROM PriceTable;'

While this runs, I can't apply different formulas for products sold in different states. This will just return one formula per product regardless of the state. And even with a bunch of conditional logic, it seems pretty clunky. Is there a better way to approach this situation?

Matt · Accepted Answer · 2016-06-13 20:58:38Z

1

First be very very careful with accepting a users input and building dynamic query on it! this is a pretty large security risk, read up on SQL injection etc.

Second, the only way you can use the information in a column to actually do the formula is dynamic sql. Or perhaps case statements instead? How many ways and columns are really involved? Can you have a table of available calculation methods and have the user choose the one they want? Then you can write a case statement that calculates based on their choice. Or union all and multiple select statements based on their choice whichever will help you organize your thoughts.

If you really want to stay on the route you are on and security is NOT a concern. Just move forward with more dynamic sql and use temp tables to store results. So have a cursor that figures out all of the different formulas (or states) with a group by. Then step through that cursor building an additional condition on your select statement (e.g. where state = 'CA' or FormulaStatment = 'ProductWeight * ShippingCost') then Insert your results to temp table. After cursor select your temp table.

answered Jun 13, 2016 at 20:58

Matt

14.5k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C Dieguez Over a year ago

Yes, the security risks are definitely worth noting. I have the form set up so that users can only build formulas using a predetermined set of field names and mathematical operators. It's also a very limited set of users that would be able to change the calculations. We're currently working with 8 calculations, but the number will increase, potentially double that. I appreciate and will look into both ideas. I'm not wed to using dynamic SQL, whichever gets the job done most efficiently.

Matt Over a year ago

Well without the security concern the dynamic sql will be your least overall maitenance in the future as formulas grow and is actually pretty quick to code. On the other side of the issue 8 or 16 conditional statements aren't that hard to code either. Good luck!

Collectives™ on Stack Overflow

Build SQL Query from User Input

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related