Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WHERE 1 = 1


WHERE 1 = 1

Author
Message
Shaira
Shaira
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 276
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!!
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 4554
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/
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3252 Visits: 11771
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


Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 4554
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/
Shaira
Shaira
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 276
Thanks to both of you for your quick replies!
andrewd.smith
andrewd.smith
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 3232
"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


mtillman-921105
mtillman-921105
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 3852
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
Henry Treftz
Henry Treftz
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 365
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.
Rick.Cornell.SME
Rick.Cornell.SME
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5305 Visits: 208
I have seen this too in the USP's where 1 = 1???
Kiran Polamarasetty
Kiran Polamarasetty
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 6
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search