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


WHERE 1 = 1


WHERE 1 = 1

Author
Message
Shaira
Shaira
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: 1164 Visits: 278
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13684 Visits: 4955
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
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35766 Visits: 11933
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13684 Visits: 4955
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
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: 1164 Visits: 278
Thanks to both of you for your quick replies!
andrewd.smith
andrewd.smith
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4948 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3226 Visits: 3854
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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

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

Group: General Forum Members
Points: 6975 Visits: 208
I have seen this too in the USP's where 1 = 1???
Kiran Polamarasetty
Kiran Polamarasetty
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

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