CODEWITHSUNDEEP
sqlsql-server
C Dieguez
C Dieguez

Reputation: 342

Build SQL Query from User Input

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?

Upvotes: 0

Views: 232

Answers (1)

Matt
Matt

Reputation: 14361

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.

Upvotes: 1

Related Questions