WHERE 1 = 1
-
I am afraid I am really going to show my newbie-ness with this question …
I see lots of stored procedures with ‘WHERE 1 = 1’ in them and I’m wondering what the purpose of this is. 1 will always = 1 so I am confused why this is used. Will someone please explain … in newbie language 🙂 Thanks!!
-
Actually, your question is not as bad as you think it is! 😉
As far as I know, this is used for testing . . . the clause "where 1 = 1" is generally a placeholder for what will eventually be a conditional statement. "where 1=1" will always eval to "true," where "where 1 = 2" will always eval to "false."
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
It is a way to create an endless loop that you will exit using the BREAK command, instead of when the test condition is true.
while 1=1
begin
<Do something>
if <some condition is true>break
<Do something else>
if <some other condition is true>break
end
-
Michael Valentine Jones (8/13/2010)
It is a way to create an endless loop that you will exit using the BREAK command, instead of when the test condition is true.while 1=1
begin
<Do something>
if <some condition is true>break
<Do something else>
if <some other condition is true>break
end
Actually, I like this explanation better!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/ -
Thanks to both of you for your quick replies!
-
"WHERE (1 = 1)" is sometimes convenient when generating dynamic SQL.
It avoids having to determine if a particular filter criterion in the WHERE clause is the first in the list, so all filter criteria can be prefixed with "AND". For example:
CREATE PROCEDURE MyDynamicQuery@Param1 varchar(20) = NULL,
@Param2 varchar(50) = NULL,
@Param3 datetime = NULL
AS
BEGIN
DECLARE @sql nvarchar(1000)
DECLARE @paramList nvarchar(1000)
SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime'
SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)'
IF NOT (@Param1 IS NULL)
SELECT @sql = @sql + N' AND (Col1 = @P1)'
IF NOT (@Param2 IS NULL) BEGIN
SELECT @Param2 = @Param2 + '%'
SELECT @sql = @sql + N' AND (Col2 LIKE @P2)'
END
IF NOT (@Param3 IS NULL)
SELECT @sql = @sql + N' AND (Col3 >= @P3)'
--PRINT @sql
EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3
RETURN
END
-
andrewd.smith (8/13/2010)
"WHERE (1 = 1)" is sometimes convenient when generating dynamic SQL.It avoids having to determine if a particular filter criterion in the WHERE clause is the first in the list, so all filter criteria can be prefixed with "AND". For example:
CREATE PROCEDURE MyDynamicQuery@Param1 varchar(20) = NULL,
@Param2 varchar(50) = NULL,
@Param3 datetime = NULL
AS
BEGIN
DECLARE @sql nvarchar(1000)
DECLARE @paramList nvarchar(1000)
SELECT @paramList = N'@P1 varchar(20), @P2 varchar(50), @P3 datetime'
SELECT @sql = N'SELECT Col1, Col2, Col3 FROM dbo.MyTable WHERE (1=1)'
IF NOT (@Param1 IS NULL)
SELECT @sql = @sql + N' AND (Col1 = @P1)'
IF NOT (@Param2 IS NULL) BEGIN
SELECT @Param2 = @Param2 + '%'
SELECT @sql = @sql + N' AND (Col2 LIKE @P2)'
END
IF NOT (@Param3 IS NULL)
SELECT @sql = @sql + N' AND (Col3 >= @P3)'
--PRINT @sql
EXEC sp_executesql @sql, @paramList, @Param1, @Param2, @Param3
RETURN
END
Interesting! Ray, you're right too.
As a side note, I've seen people use WHERE 1 = 0 used too for creating a temp table with no records (i.e., copying the structure into an empty temp table)
select *
into #tmp
from #AnotherTable
where 1=0
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
-
I see that a lot with dynamic SQL I used to search my reversed dynamic SQL for erehw and remove it if it started the trimmed string, but 1=1 works just as well.
-
I have seen this too in the USP's where 1 = 1???
-
There is a simple logic behind putting where 1=1 while creating the dynamic queries.
For ex: for getting user information based on the few conditions like with Firstname or Lastname or both.
SqlQuery= "Select * from tblUser Where 1=1 ";
If(firstname is not null)
SqlQuery=SqlQuery & " And Firstname="+@Firstname ;
If(Lastname is not null)
SqlQuery=SqlQuery & " And Lastname ="+@Lastname ;
Explanation:
If we have not mentioned Where 1=1 in above query there could be a more logical code we have to write for checking for adding the AND in front of Firstname and Lastname. So for avoiding that logical code and performance point many are using "WHERE 1=1" in the dynamic queries.
Please let me know it helps you are not.
-
This is a pretty old thread but you are correct this type of approach will work for that sort of "catch all" query. However, Gail wrote a blog post about this topic that will perform the same type of dynamic filtering but will maintain a much higher level of performance. Read her post here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
mhk0m3 (8/6/2012)
[font="Arial"]:-)Let us look at another example why ' or 1=1-- is important. Other than bypassing login, it is also possible to view extra information that is not normally available. Take an asp page that will link you to another page with the following URL:http://duck/index.asp?category=food
In the URL, 'category' is the variable name, and 'food' is the value assigned to the variable. In order to do that, an ASP might contain the following code (OK, this is the actual code that we created for this exercise):
v_cat = request("category")
sqlstr="SELECT * FROM product WHERE PCategory='" & v_cat & "'"
set rs=conn.execute(sqlstr)
As we can see, our variable will be wrapped into v_cat and thus the SQL statement should become:
SELECT * FROM product WHERE PCategory='food'
FOR BEST TUTORIAL GO TO
:w00t:[/font]
I can't see how this is relevant to the thread. You have simply built a query which is open to SQL injection.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Another use for 1=1 is where you are running ad-hoc sql and want to make several changes. For example you can replace:
where firstcol = firstval
and secondcol = secondval
and thirdcol > thirdvalwith:
where 1=1
and firstcol = firstval
and secondcol = secondval
and thirdcol > thirdvalUsing this second syntax you can switch in and out each statement with a: --
Whereas with the first syntax if you wanted to remove firstcol = firstval you would have to copy/paste it out to make the change.Though, as Jeff Moden has just said in reply to another one of my posts, you wouldn't want to use this in production code (it is a "code smell").
Also:
where 1=0
or firstcol = firstval
or secondcol = secondval
or thirdcol > thirdvalhttp://90.212.51.111 domain
-
Using "where 1 = 1" can also change the execution plan of a query.
Take a look at this great article from Brent Ozar Unlimited site: https://www.brentozar.com/archive/2017/06/query-plans-trivial-optimization-vs-simple-parameterization/
It shows some interesting behavior from the execution plan when using "where 1 = 1" or better yet "where 1 = (select 1)".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply