During the maintenance of a legacy system I noticed the following procedure :
DECLARE @sql AS varchar(MAX);
DECLARE @param as varchar(50);
SET @sql = 'SELECT * FROM Destinatario where 1 = 1';
IF(@param IS NOT NULL)
SET @sql = (@sql + ' AND Nome = ''' + @param + '''');
EXEC(@sql)
The first thing that caught my attention was the condition where 1 = 1
This condition does not appear to have any reason at all, as removing it gives the same effect.
However, when the where conditions are generated dynamically, and the programmer adds them withAND within if s, because they depend on other factors in the program that generates SQL, it is clear :
If there is no additional condition, the query remains so ...
SELECT * FROM Destinatario where 1 = 1
... but in case, if the condition is true shortly after your example, it can be:
SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto"
Note that if there were no WHERE 1=1 , it would look like this, and would not work:
SELECT * FROM Destinatario AND Nome = "Roberto"
-- temos um erro aqui --^
For a AND condition only, the author could have put WHERE within the condition, but if several conditions exist, WHERE 1=1 would allow several separate conditions, mounted by if s different:
SELECT * FROM Destinatario where 1 = 1
SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto"
SELECT * FROM Destinatario where 1 = 1 AND Cidade = "Santos"
SELECT * FROM Destinatario where 1 = 1 AND Nome = "Roberto" AND Cidade = "Santos"
So, AND Nome could be in if and AND Cidade in another, and all would work regardless of whether there is already a if in the code that generates SQL .
As mentioned by @bfavaretto, CakePHP, for example, uses this technique.
Particularly, I think it's better to use cleaner manners than how it was used, such as concatenating withAND only when dealing with the 2nd condition on, but each has its own criteria, and at any given time a certain technique may be better than another.
Exiting this specific example of usage, it may even happen that someone uses a "% neutral"% when testing a condition during code development, eliminating the original condition (equivalent to WHERE , which would also the initial explanation), but this is another story.
In our specific case, the% w / o% of this line confirms the initial hypothesis of the optional parameter with WHERE true :
IF(@param IS NOT NULL) -- <== Aqui
SET @sql = (@sql + ' AND Nome = ''' + @param + '''');
There are other interesting points to be raised for this solution with WHERE 1 = 1 .
One advantage of this technique is that the code ends up being cleaner , therefore there is no need to control the presence of WHERE vs AND/OR , keeping the order of the filters going in the query and if any filter will go into the query.
There is a recurring question about the impact of performance on the presence database of WHERE 1 = 1 , regardless of whether there are other filters or not. What can I say to most databases does not suffer any performance impact when using this technique.
By doing an impact analysis on the execution plan of the Sql Server, with 10,000 records in a table, there was no change between the plan containing WHERE 1 = 1 and the plan without it.
Some databases also support WHERE 1 , although this is not the recommended option because it is not part of the ANSI standard.