SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WHERE 1 = 1


WHERE 1 = 1

Author
Message
Shaira
Shaira
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6802 Visits: 4681
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14785 Visits: 11848
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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6802 Visits: 4681
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-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 276
Thanks to both of you for your quick replies!
andrewd.smith
andrewd.smith
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: 2644 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1532 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 Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 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.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

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

Group: General Forum Members
Points: 23 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