SQL where clause variability up against coding standards
-
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions. What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures. This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum. Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well. I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient. What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon. something like this select * from people where if @fNameActive then fname like @fNameParam and if @dobActive then dob = @dobParam Otherwise name and dob should not be evaluated. I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case. Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well. Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials. So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner? I can't be the only developer to run up against this wall. But it's so complicated that
-
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions. What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures. This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum. Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well. I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient. What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon. something like this select * from people where if @fNameActive then fname like @fNameParam and if @dobActive then dob = @dobParam Otherwise name and dob should not be evaluated. I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case. Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well. Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials. So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner? I can't be the only developer to run up against this wall. But it's so complicated that
Was there ever a feature added to support what you were thinking of doing? No. What you do in a case like this is either write your own syntax parser and query language to break down what is typed into a search box, or boxes, and build the query yourself, or use a dedicated indexing and search engine, like Lucene, to do it for you. In my latest web app, I went with the home-built route. I have a single search box where you type your query. It understands a specific, home brewed, query language. That query statement gets tokenized and broken down into an "abstract syntax tree", or AST. That tree is then sent to another module that walks the tree, interprets it, and builds an SQL SELECT WHERE clause from it, complete with parenthesis, AND/OR operators. One important part you have to remember is building proper indexes on the database to support the queries to make them more performant.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions. What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures. This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum. Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well. I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient. What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon. something like this select * from people where if @fNameActive then fname like @fNameParam and if @dobActive then dob = @dobParam Otherwise name and dob should not be evaluated. I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case. Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well. Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials. So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner? I can't be the only developer to run up against this wall. But it's so complicated that
Brian L Hughes wrote:
Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials.
That sounds like the EU. We are having a tiny problem with X, let's ban EVERYTHING that resembles it. You can make those rules, but I will not ever bend software to fit some idiot idea. You query stale data from a snapshot, from a readonly DB, as is best practice, using a locked down account. WTF is going "Stored Procedures" going to do in terms of added safety??
Brian L Hughes wrote:
It looks to me like the only way to get it to work is by omitting the column.
In SQL Server it's called "free text search".
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions. What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures. This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum. Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well. I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient. What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon. something like this select * from people where if @fNameActive then fname like @fNameParam and if @dobActive then dob = @dobParam Otherwise name and dob should not be evaluated. I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case. Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well. Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials. So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner? I can't be the only developer to run up against this wall. But it's so complicated that
Brian L Hughes wrote:
I had an app that users could query tables. The app form contained many text boxes
That is pretty indefinite. Especially as it says 'tables' plural. Also it is not clear how the users build there statements. For example is it only 'and' or does it allow 'or' clauses also. ------------------------------------------------------- There are in fact many tables and each table only has a few columns. Also there are no joins. But 'many' actually means not all that many. So perhaps 10 tables and 3 columns each. Then if it was me I would generate (not manually code) the variations to produce the procs. This would happen during the build not at run time. There would be a process to determine whether changes were made so that it would not require updating the procs every release. For example there would be a separate build that only runs when something is known to have changed. Such as adding a new table. ------------------------------------------------------- But lets presume you had one table with a lot of columns. First the UI should limit the number of clauses that can be created. That should be true regardless of any other solution. Nothing in a computer should be unlimited and in a case like this at some point one reaches diminishing returns. Solutions. 1. Any process should have a process for exceptions. If it does not then the process itself is flawed (I spent 15 years in process control groups, principal process author and sometimes sole process controller along with 5 years in security process.) So either use the exception process or insist that one is added. Process control exceptions should of course be documented. Probably more so than any other process control step. So that would need to be done also. 2. Pass an array to the proc. The proc builds dynamic SQL and then executes it. There will be a limit to the size of the array. This of course is just a variation of your solution but moving where the code executes. 3. Create the procs dynamically. I would go ballistic on this solution if there was more than about 30 procs (per table). Even with generation at some point this becomes a maintenance problem.
-
I'm retired but when I was working I had a major problem. I had an app that users could query tables. The app form contained many text boxes which could be filled with values if they wanted to see the result. If the user typed in a box I'd concat a parameterized condition to the final SQL statement where clause including that column. Then we were hit with new coding standards. I could no longer concat SQL and had to use stored procedures. No exceptions. What I decided to do was create a stored procedure for every possible combination of search boxes on the form. The only impact and drawback that I saw to this method was having to code and maintain many stored procedures. This was quite a few years ago. At the time I tried searching the www for some kind of SQL solution, but I couldn't describe what I was looking for into a www search, it was too complicated, too many words. At the time I didn't ask the question on this forum. Basically I was looking for a way in a stored procedure not to include a particular column in a where clause. It couldn't test the actual parameter variable for null because the app form allowed the user to specifically request that they wanted to see results if the column were null in any of the rows as well. I didn't want the stored procedure to be a giant mess of if statements and multiple SQL statements with varying where clauses because that wouldn't very efficient. What I thought I wanted was a new kind of SQL operator that could be used to specify that the column should only be included in the final query plan if a smart parameter property were triggered from the app that it should be acted upon. something like this select * from people where if @fNameActive then fname like @fNameParam and if @dobActive then dob = @dobParam Otherwise name and dob should not be evaluated. I think the proper solution would be to allow exceptions to the mandatory stored procedure rule. I think SQL was designed to allow programmers to build the where clause as needed in this specific case. Allowing for parameterized concatenation would enable even more powerful searches where the user can specify less than or greater than as well. Our rules makers meant well. They were trying to close the loop on web sites that concatenated login credentials. So was there ever a feature added to SQL that allowed us to fiddle with a where clause in a stored procedure in this manner? I can't be the only developer to run up against this wall. But it's so complicated that
Brian L Hughes wrote:
SELECT * FROM people WHERE ( @nameactive = 1 AND name like @nameparam ) OR ( @dobActive = 1 AND dob = @dobParam) Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
So, you want to only have Georges born on the 30th Feb? If so, why no use De Morgan's laws - Wikipedia[^] and reverse your tests e.g.
SELECT * FROM people
...
WHERE (@nameactive = 0 OR name like @nameparam)
AND (@dobactive = 0 OR dob = @dobparam)This should just treat unwanted criteria as true and just AND wanted criteria.
-
Brian L Hughes wrote:
SELECT * FROM people WHERE ( @nameactive = 1 AND name like @nameparam ) OR ( @dobActive = 1 AND dob = @dobParam) Well it seemed to work but failed to narrow down to a specific row. Hypothetically if fnameparam were george and dobparam was 2010/2/30 it would return all georges and all people who were born on feb 30th, so no, it should return no rows if there are no georges born on feb 30th. Yes, by George I am joking about feb 30th.
So, you want to only have Georges born on the 30th Feb? If so, why no use De Morgan's laws - Wikipedia[^] and reverse your tests e.g.
SELECT * FROM people
...
WHERE (@nameactive = 0 OR name like @nameparam)
AND (@dobactive = 0 OR dob = @dobparam)This should just treat unwanted criteria as true and just AND wanted criteria.
Where I either don't want this column included or the column matches param, then AND them across the where clause and presto! I tested it on mysql and it worked! It's kind of funny that I couldn't come up with a solution for this after years of sql coding. I will admit that sometimes I can't figure out multiple combinations of AND and OR tags in code. Is the sql engine is smart enough not to include any actual column testing at runtime if the "include this column" param is 0?
-
Where I either don't want this column included or the column matches param, then AND them across the where clause and presto! I tested it on mysql and it worked! It's kind of funny that I couldn't come up with a solution for this after years of sql coding. I will admit that sometimes I can't figure out multiple combinations of AND and OR tags in code. Is the sql engine is smart enough not to include any actual column testing at runtime if the "include this column" param is 0?
The phrase you are looking for is 'short circuiting'. https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated[^] quotes an SQL standard that says
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...]
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
I have not found any specific answer for MySql but IIRC MySql gives you a choice of engines so it might depend on the engine. The answer for MS SQL SERVER (according to Understanding T-SQL Expression Short-Circuiting – SQLServerCentral[^]) is that short circuiting does happen (but that is specific to that one environment). It is easy to test. If you have a WHERE clause like
WHERE NULL = NULL OR 1/0 = 1
then it will deliver TRUE if short circuiting is implemented and throw an error (trying to divide by zero) if not implemented.
-
The phrase you are looking for is 'short circuiting'. https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated[^] quotes an SQL standard that says
ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf
6.3.3.3 Rule evaluation order
[...]
Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.
I have not found any specific answer for MySql but IIRC MySql gives you a choice of engines so it might depend on the engine. The answer for MS SQL SERVER (according to Understanding T-SQL Expression Short-Circuiting – SQLServerCentral[^]) is that short circuiting does happen (but that is specific to that one environment). It is easy to test. If you have a WHERE clause like
WHERE NULL = NULL OR 1/0 = 1
then it will deliver TRUE if short circuiting is implemented and throw an error (trying to divide by zero) if not implemented.
jsc42 wrote:
I have not found any specific answer for MySql
Interesting. I couldn't either. I know it happens in C++ so I attempted to find that expression ("short circuit") in the standard and as far as I can tell it does not exist. I used the actual book to look it up. So for that (C++) it is expressed like the following "The operators && and || will not evaluate their second argument unless doing so is necessary" For C it is expressed as the following "Expressions connected by && or || are evaluated left to right, and evaluation stops as soon as the truth of falsehood of the result is known" Then I looked for that terminology in MySQL docs (8) and found nothing on the page that defines AND and OR that would suggest that. I also attempted the same for PL/SQL and also found nothing.