[quote=“Tim”]Essentially I want to query a table to choose logic to be used within a second query of another table, but I cannot get this second query to execute because I cannot escape the varchar format of the stored query string.[/quote]Actually, using results from one query to create a where clause for another query is reasonable. However, the format you’ve shown doesn’t match any normal expression engine. If you are only using OR, after retrieving the “expression”, I would use a regular expression to replace each integer in your “expression” with the actual comparison including that number. Something like:
exp = system.db.runScalarQuery("Select column2 from myTable Where .....")
expandedexp = re.sub(r'([0-9]+)', r'column1=\1', exp)
results = system.db.runQuery("Select * From ... Where "+expandedexp)If you expect multiple results from the first query, you’ll have to rearrange this to either execute the second query once for each row of query 1, or combine the expressions from multiple rows into one huge where clause for query 2. The latter approach has to stay within your SQL server’s line length limit.
You say some expressions will use AND. If so, the above regular expression won’t work – you’ll need to figure out some way to transform your expression into a valid SQL fragment, or just store valid SQL directly.
Note that this isn’t any form of escaping – there is no escaping or standard SQL function that will parse your logic on a row by row basis. Your only other choice is a stored function on the SQL server that will parse and EXEC your logic one sample at a time. Then you’d use:
results = system.db.runQuery("Select * From ... Where myStoredFunction(column1, column2)")